*&---------------------------------------------------------------------* *& Report ZS_TABLES_WITHOUT_AUTH_GROUP *&---------------------------------------------------------------------* *& Purpose: List tables not assigned to a table authorisation group *& Author : Daniel Berlin *& Version: 1.1.2 *& License: CC BY 3.0 (https://creativecommons.org/licenses/by/3.0/) *&---------------------------------------------------------------------* REPORT zs_tables_without_auth_group. TABLES: dd02l, tddat, tdevc. TYPE-POOLS: icon, slis. DATA: gv_ucomm TYPE sy-ucomm, BEGIN OF gt_tablist OCCURS 0, tabname TYPE dd02l-tabname, " Table name ddtext TYPE dd02t-ddtext, " Table description cclass TYPE tddat-cclass, " Authorisation group bezei TYPE tbrgt-bezei, " Auth. group description hasdata TYPE icon-id, " Table contains data clidep TYPE icon-id, " Table is client-dependent protokoll TYPE icon-id, " Logging of data changes contflag TYPE dd02l-contflag, " Delivery class devclass TYPE tadir-devclass, " Package tabclass TYPE dd02l-tabclass, " Table category alvbox TYPE c, " ALV checkbox status END OF gt_tablist, gv_total TYPE i. SELECTION-SCREEN: BEGIN OF SCREEN 101 AS SUBSCREEN. SELECT-OPTIONS: r_table FOR dd02l-tabname. SELECTION-SCREEN: SKIP, BEGIN OF LINE, COMMENT 1(33) n_count. PARAMETERS: p_count AS CHECKBOX USER-COMMAND cnt. SELECTION-SCREEN: END OF LINE, BEGIN OF LINE, COMMENT 3(31) n_empty. PARAMETERS: p_empty AS CHECKBOX. SELECTION-SCREEN: END OF LINE, END OF SCREEN 101. SELECTION-SCREEN: BEGIN OF SCREEN 102 AS SUBSCREEN. SELECT-OPTIONS: r_autgrp FOR tddat-cclass. SELECTION-SCREEN: SKIP. SELECT-OPTIONS: r_tabcls FOR dd02l-tabclass, r_viewcl FOR dd02l-viewclass, r_contfl FOR dd02l-contflag. SELECTION-SCREEN: SKIP. SELECT-OPTIONS: r_devcls FOR tdevc-devclass. SELECTION-SCREEN: END OF SCREEN 102. SELECTION-SCREEN: BEGIN OF TABBED BLOCK a FOR 15 LINES, TAB (10) n_tables USER-COMMAND ucomm1 DEFAULT SCREEN 101, TAB (20) n_advanc USER-COMMAND ucomm2 DEFAULT SCREEN 102, END OF BLOCK a. INITIALIZATION. AUTHORITY-CHECK OBJECT 'S_TABU_DIS' ID 'ACTVT' FIELD '03' ID 'DICBERCLS' DUMMY. IF sy-subrc <> 0. MESSAGE 'Missing authorisation.' TYPE 'E'. "#EC NOTEXT LEAVE PROGRAM. ENDIF. " --- Titles and texts n_tables = 'Selection'. "#EC NOTEXT n_count = 'Check tables for data'. "#EC NOTEXT n_empty = 'Show empty tables'. "#EC NOTEXT n_advanc = 'Advanced options'. "#EC NOTEXT " --- Exclude Structures and Append Structures r_tabcls-sign = 'E'. r_tabcls-option = 'EQ'. r_tabcls-low = 'INTTAB'. APPEND r_tabcls. r_tabcls-low = 'APPEND'. APPEND r_tabcls. " --- Exclude Help -, Structure -, Append Views and View Structures r_viewcl-sign = 'E'. r_viewcl-option = 'EQ'. r_viewcl-low = 'H'. APPEND r_viewcl. r_viewcl-low = 'S'. APPEND r_viewcl. r_viewcl-low = 'A'. APPEND r_viewcl. r_viewcl-low = space. APPEND r_viewcl. " --- Exclude delivery class 'L' (tables for storing temporary data) r_contfl-sign = 'E'. r_contfl-option = 'EQ'. r_contfl-low = 'L'. APPEND r_contfl. " --- Include authorisation group '&NC&' and (empty) r_autgrp-sign = 'I'. r_autgrp-option = 'EQ'. r_autgrp-low = '&NC&'. APPEND r_autgrp. r_autgrp-low = space. APPEND r_autgrp. " --- Exclude non-transportable packages r_devcls-sign = 'E'. r_devcls-option = 'EQ'. SELECT devclass INTO r_devcls-low FROM tdevc WHERE korrflag <> 'X'. APPEND r_devcls. ENDSELECT. AT SELECTION-SCREEN. gv_ucomm = sy-ucomm. AT SELECTION-SCREEN OUTPUT. LOOP AT SCREEN. CASE screen-name. WHEN 'P_EMPTY'. IF gv_ucomm = 'CNT'. p_empty = p_count. ENDIF. IF p_count = 'X'. screen-input = 1. ELSE. screen-input = 0. ENDIF. MODIFY SCREEN. ENDCASE. ENDLOOP. START-OF-SELECTION. CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' EXPORTING text = 'Searching...'. "#EC NOTEXT " --- Get all tables, views, etc. SELECT * FROM dd02l INTO CORRESPONDING FIELDS OF gt_tablist WHERE tabname IN r_table AND tabclass IN r_tabcls AND ( tabclass <> 'VIEW' OR viewclass IN r_viewcl ) AND as4local = 'A' AND contflag IN r_contfl ORDER BY tabname. IF gt_tablist-clidep = 'X'. gt_tablist-clidep = icon_okay. ELSE. gt_tablist-clidep = icon_dummy. ENDIF. " --- Get table description SELECT ddtext UP TO 1 ROWS FROM dd02t INTO gt_tablist-ddtext WHERE tabname = gt_tablist-tabname AND ddlanguage = sy-langu AND as4local = 'A'. ENDSELECT. " --- Get authorisation group SELECT cclass UP TO 1 ROWS FROM tddat INTO gt_tablist-cclass WHERE tabname = gt_tablist-tabname. ENDSELECT. " --- Table not found or authorisation group explicitly selected IF sy-dbcnt = 0 OR gt_tablist-cclass IN r_autgrp. " --- Get authorisation group description IF sy-dbcnt > 0 AND gt_tablist-cclass <> space. SELECT bezei UP TO 1 ROWS FROM tbrgt INTO gt_tablist-bezei WHERE spras = sy-langu AND brobj = 'S_TABU_DIS' AND brgru = gt_tablist-cclass. ENDSELECT. ENDIF. " --- Get package SELECT devclass UP TO 1 ROWS FROM tadir INTO gt_tablist-devclass WHERE pgmid = 'R3TR' AND object IN ('TABL', 'VIEW') AND obj_name = gt_tablist-tabname. ENDSELECT. " --- Filter by selected package(s) IF gt_tablist-devclass NOT IN r_devcls. CLEAR gt_tablist. CONTINUE. ENDIF. " --- Get logging flag SELECT COUNT(*) UP TO 1 ROWS FROM dd09l WHERE tabname = gt_tablist-tabname AND as4local = 'A' AND protokoll = 'X'. IF sy-dbcnt > 0. gt_tablist-protokoll = icon_okay. ELSE. gt_tablist-protokoll = icon_dummy. ENDIF. IF p_count = 'X'. TRY. " --- Check, if the table is empty SELECT COUNT(*) UP TO 1 ROWS FROM (gt_tablist-tabname). IF sy-dbcnt > 0. gt_tablist-hasdata = icon_okay. ELSE. IF p_empty <> 'X'. CLEAR gt_tablist. CONTINUE. ENDIF. gt_tablist-hasdata = icon_dummy. ENDIF. CATCH cx_sy_sql_error. " --- SQL error (e.g. due to a defective view) gt_tablist-hasdata = icon_failure. ENDTRY. ENDIF. APPEND gt_tablist. ENDIF. CLEAR gt_tablist. ENDSELECT. " --- Total number of tables examined gv_total = sy-dbcnt. PERFORM alv_show_result. *&---------------------------------------------------------------------* *& Form alv_show_result *&---------------------------------------------------------------------* FORM alv_show_result. DATA: lt_fieldcat TYPE slis_t_fieldcat_alv WITH HEADER LINE, ls_layout TYPE slis_layout_alv. " --- Field catalogue lt_fieldcat-fieldname = 'TABNAME'. lt_fieldcat-ref_tabname = 'DD02L'. lt_fieldcat-key = 'X'. APPEND lt_fieldcat. CLEAR lt_fieldcat. lt_fieldcat-fieldname = 'DDTEXT'. lt_fieldcat-ref_tabname = 'DD02T'. APPEND lt_fieldcat. CLEAR lt_fieldcat. lt_fieldcat-fieldname = 'CCLASS'. lt_fieldcat-ref_tabname = 'TDDAT'. lt_fieldcat-outputlen = 8. APPEND lt_fieldcat. CLEAR lt_fieldcat. lt_fieldcat-fieldname = 'BEZEI'. lt_fieldcat-ref_tabname = 'TBRGT'. lt_fieldcat-outputlen = 14. APPEND lt_fieldcat. CLEAR lt_fieldcat. lt_fieldcat-fieldname = 'HASDATA'. IF p_count = 'X'. lt_fieldcat-seltext_s = 'Data'. "#EC NOTEXT lt_fieldcat-seltext_m = 'Table contains data'. "#EC NOTEXT lt_fieldcat-icon = 'X'. lt_fieldcat-outputlen = 4. ELSE. lt_fieldcat-tech = 'X'. ENDIF. APPEND lt_fieldcat. CLEAR lt_fieldcat. lt_fieldcat-fieldname = 'CLIDEP'. lt_fieldcat-ref_tabname = 'DD02L'. lt_fieldcat-icon = 'X'. lt_fieldcat-outputlen = 8. APPEND lt_fieldcat. CLEAR lt_fieldcat. lt_fieldcat-fieldname = 'PROTOKOLL'. lt_fieldcat-ref_tabname = 'DD09L'. lt_fieldcat-icon = 'X'. lt_fieldcat-outputlen = 4. APPEND lt_fieldcat. CLEAR lt_fieldcat. lt_fieldcat-fieldname = 'CONTFLAG'. lt_fieldcat-ref_tabname = 'DD02L'. lt_fieldcat-just = 'C'. lt_fieldcat-outputlen = 8. APPEND lt_fieldcat. CLEAR lt_fieldcat. lt_fieldcat-fieldname = 'DEVCLASS'. lt_fieldcat-ref_tabname = 'TADIR'. APPEND lt_fieldcat. CLEAR lt_fieldcat. lt_fieldcat-fieldname = 'TABCLASS'. lt_fieldcat-ref_tabname = 'DD02L'. APPEND lt_fieldcat. CLEAR lt_fieldcat. " --- Layout ls_layout-box_fieldname = 'ALVBOX'. * ls_layout-colwidth_optimize = 'X'. ls_layout-no_input = 'X'. ls_layout-zebra = 'X'. CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY' EXPORTING i_callback_program = sy-repid i_callback_user_command = 'ALV_CALLBACK_USER_COMMAND' i_callback_top_of_page = 'ALV_CALLBACK_TOP_OF_PAGE' is_layout = ls_layout it_fieldcat = lt_fieldcat[] i_save = 'X' TABLES t_outtab = gt_tablist EXCEPTIONS OTHERS = 1. ENDFORM. "alv_show_result *&---------------------------------------------------------------------* *& Form alv_callback_top_of_page *&---------------------------------------------------------------------* FORM alv_callback_top_of_page. "#EC CALLED DATA: ls_header TYPE slis_listheader, lt_header TYPE slis_t_listheader, li_found TYPE i, lc_found(10) TYPE c, lc_total(10) TYPE c. ls_header-typ = 'S'. DESCRIBE TABLE gt_tablist LINES li_found. lc_found = li_found. lc_total = gv_total. CONCATENATE 'In total' lc_found 'of' lc_total "#EC NOTEXT 'tables match.' INTO ls_header-info SEPARATED BY space. APPEND ls_header TO lt_header. CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE' EXPORTING it_list_commentary = lt_header. ENDFORM. "alv_callback_top_of_page *&---------------------------------------------------------------------* *& Form alv_callback_user_command *&---------------------------------------------------------------------* FORM alv_callback_user_command "#EC CALLED USING pv_ucomm LIKE sy-ucomm ps_selfield TYPE slis_selfield. READ TABLE gt_tablist INDEX ps_selfield-tabindex. IF pv_ucomm = '&IC1'. CASE ps_selfield-fieldname. WHEN 'TABNAME'. PERFORM goto_se11 USING gt_tablist-tabname. WHEN 'DDTEXT'. PERFORM goto_se16 USING gt_tablist-tabname. WHEN 'CCLASS'. PERFORM goto_sm30 USING 'V_DDAT_54'. * WHEN 'BEZEI'. WHEN 'HASDATA'. PERFORM show_table_count USING gt_tablist-tabname. WHEN 'CLIDEP'. CALL FUNCTION 'F4IF_FIELD_VALUE_REQUEST' EXPORTING tabname = 'DD02L' fieldname = ps_selfield-fieldname EXCEPTIONS OTHERS = 1. WHEN 'PROTOKOLL'. PERFORM goto_se13 USING gt_tablist-tabname. WHEN 'CONTFLAG'. CALL FUNCTION 'F4IF_FIELD_VALUE_REQUEST' EXPORTING tabname = 'DD02L' fieldname = ps_selfield-fieldname EXCEPTIONS OTHERS = 1. WHEN 'DEVCLASS'. PERFORM goto_se21 USING gt_tablist-devclass. WHEN 'TABCLASS'. CALL FUNCTION 'F4IF_FIELD_VALUE_REQUEST' EXPORTING tabname = 'DD02L' fieldname = ps_selfield-fieldname EXCEPTIONS OTHERS = 1. ENDCASE. ENDIF. ENDFORM. "alv_callback_user_command *&---------------------------------------------------------------------* *& Form goto_se11 *&---------------------------------------------------------------------* FORM goto_se11 USING tabname LIKE rsedd0-ddobjname. CALL FUNCTION 'RS_DD_SHOW' EXPORTING objname = tabname objtype = 'U' EXCEPTIONS OTHERS = 1. IF sy-subrc <> 0. MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ENDIF. ENDFORM. "goto_se11 *&---------------------------------------------------------------------* *& Form goto_se13 *&---------------------------------------------------------------------* FORM goto_se13 USING tabname LIKE dd09v-tabname. " --- Technical settings not available for these table categories CASE gt_tablist-tabclass. WHEN 'APPEND'. EXIT. WHEN 'INTTAB'. EXIT. WHEN 'VIEW'. EXIT. ENDCASE. DATA: lt_bdcdata LIKE bdcdata OCCURS 0 WITH HEADER LINE. lt_bdcdata-program = 'SAPMSEDS'. lt_bdcdata-dynpro = 10. lt_bdcdata-dynbegin = 'X'. lt_bdcdata-fnam = 'DD09V-TABNAME'. lt_bdcdata-fval = tabname. APPEND lt_bdcdata. CLEAR lt_bdcdata. lt_bdcdata-fnam = 'BDC_OKCODE'. lt_bdcdata-fval = 'ANZG'. APPEND lt_bdcdata. CALL FUNCTION 'ABAP4_CALL_TRANSACTION' EXPORTING tcode = 'SE13' mode_val = 'E' update_val = 'S' TABLES using_tab = lt_bdcdata EXCEPTIONS OTHERS = 1. IF sy-subrc <> 0. MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ENDIF. ENDFORM. "goto_se13 *&---------------------------------------------------------------------* *& Form goto_se16 *&---------------------------------------------------------------------* FORM goto_se16 USING tabname LIKE dd02l-tabname. CALL FUNCTION 'RS_TABLE_LIST_CREATE' EXPORTING table_name = tabname EXCEPTIONS OTHERS = 1. IF sy-subrc <> 0. MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ENDIF. ENDFORM. "goto_se16 *&---------------------------------------------------------------------* *& Form goto_se21 *&---------------------------------------------------------------------* FORM goto_se21 USING devclass LIKE trdevclass-devclass. CALL FUNCTION 'TR_SHOW_DEVCLASS' EXPORTING iv_devclass = devclass. ENDFORM. "goto_se21 *&---------------------------------------------------------------------* *& Form goto_sm30 *&---------------------------------------------------------------------* FORM goto_sm30 USING viewname LIKE dd02v-tabname. CALL FUNCTION 'VIEW_MAINTENANCE_CALL' EXPORTING action = 'U' view_name = viewname no_warning_for_clientindep = 'X' check_ddic_mainflag = 'X' EXCEPTIONS OTHERS = 1. IF sy-subrc <> 0. MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ENDIF. ENDFORM. "goto_sm30 *&---------------------------------------------------------------------* *& Form show_table_count *&---------------------------------------------------------------------* FORM show_table_count USING tabname LIKE dd02l-tabname. DATA: count TYPE string, title TYPE string, text TYPE string. TRY. SELECT SINGLE COUNT(*) FROM (tabname) INTO count. CATCH cx_sy_sql_error. EXIT. ENDTRY. CONCATENATE 'Table' tabname "#EC NOTEXT INTO title SEPARATED BY space. CONCATENATE 'Number of entries:' count "#EC NOTEXT INTO text SEPARATED BY space. CALL FUNCTION 'POPUP_TO_DISPLAY_TEXT' EXPORTING titel = title textline1 = text. ENDFORM. "show_table_count |