Hi All,
We faced a scenario where we need to delete the data from some tables, Master data tables to be more specific.
To find out in which tables there is data was a time consuming process.
We have certain number of function modules available which can be used to delete the data for the tables, but if the master data is used elsewhere
then it wont allow you to delete it.
Thus to know in which master data tables there are records, I developed this program.
The program use wild card input to search the table names and count the number of records in them.
Note: You can enter the name of a cube, dso, info object any z table or any standard table in SAP.
(Also a few letters of the technical name of any of these objects is sufficient as an input.
It automatically takes it as a wild card input.)
But I hope this could be useful in other scenarios as well.
Hence sharing the code with all of you....
Best Regards,
Umran
SAP BI Consultant
Program details:
SE38
Create a executable program.
Technical name- Z_TABLE_CHECK
*&---------------------------------------------------------------------*
*& Report Z_TABLE_CHECK
*&
*&---------------------------------------------------------------------*
*& Umran -
*& The following program generate the list of tables and the number of records in them
*& Will be helpful in analysis for deletion of master data from tables.
*&---------------------------------------------------------------------*
REPORT Z_TABLE_CHECK.
TYPE-POOLS: SLIS.
*& For selection screen
SELECTION-SCREEN: BEGIN OF BLOCK B02 WITH FRAME TITLE TEXT-002.
PARAMETERS: P_OBJ TYPE C LENGTH 20.
SELECTION-SCREEN: END OF BLOCK B02.
*& data decleration
TYPES : BEGIN OF TY_TABLE,
TABNAME TYPE DD02L-TABNAME,
COUNT TYPE N LENGTH 20,
END OF TY_TABLE.
TYPES : BEGIN OF TY_DISPLAY,
TABNAME TYPE C LENGTH 50,
COUNT TYPE N LENGTH 10,
END OF TY_DISPLAY.
DATA: IT_TAB1 TYPE STANDARD TABLE OF TY_TABLE,
WA_TAB1 TYPE TY_TABLE,
IT_TAB2 TYPE STANDARD TABLE OF TY_TABLE,
WA_TAB2 TYPE TY_TABLE,
IT_TAB3 TYPE STANDARD TABLE OF TY_TABLE WITH HEADER LINE,
WA_TAB3 TYPE TY_TABLE,
IT_DISPLAY TYPE STANDARD TABLE OF TY_DISPLAY WITH HEADER LINE,
WA_DISPLAY TYPE TY_DISPLAY,
srch_str TYPE c LENGTH 20.
*& For grid display
DATA:
I_TITLE_GRID TYPE LVC_TITLE,
I_REPID TYPE SY-REPID,
IT_FIELDCAT TYPE SLIS_T_FIELDCAT_ALV,
WA_FIELDCAT TYPE SLIS_FIELDCAT_ALV.
*& ***************************************************************start********************************************************************
*& Input parameter
IF P_OBJ IS INITIAL .
*& The input parameter is kept to minimise the result set. Any value that can be used for wild card selection can be entered in as an input.
WRITE : ' Values for the input parameter can be any table name. Also the /BIC/ wild card is possible.
ELSE.
*& for wild card usage
srch_str = P_OBJ.
*srch_str = '/BIC/P'.
concatenate '%' srch_str '%' into srch_str.
SELECT TABNAME FROM DD02L INTO CORRESPONDING FIELDS OF TABLE IT_TAB1 WHERE AS4LOCAL = 'A' AND TABCLASS EQ 'TRANSP' AND TABNAME LIKE srch_str.
APPEND LINES OF IT_TAB1 TO IT_TAB2.
SORT IT_TAB2 ASCENDING.
DELETE ADJACENT DUPLICATES FROM IT_TAB2.
*& For couting the number of records.
LOOP AT IT_TAB2 INTO WA_TAB2.
SELECT COUNT( * ) INTO WA_TAB3-COUNT FROM (WA_TAB2-TABNAME).
WA_TAB3-TABNAME = WA_TAB2-TABNAME.
APPEND WA_TAB3 TO IT_TAB3.
ENDLOOP.
*& For grid display
LOOP AT IT_TAB3.
WA_DISPLAY-TABNAME = IT_TAB3-TABNAME.
WA_DISPLAY-COUNT = IT_TAB3-COUNT.
APPEND WA_DISPLAY TO IT_DISPLAY.
ENDLOOP.
* Assigning column names and other attributes for the grid
WA_FIELDCAT-COL_POS = 1.
WA_FIELDCAT-FIELDNAME = 'TABNAME'.
WA_FIELDCAT-REPTEXT_DDIC = 'Table name'.
WA_FIELDCAT-OUTPUTLEN = 50.
WA_FIELDCAT-EMPHASIZE = 'C300'.
WA_FIELDCAT-TABNAME = 'IT_DISPLAY'.
APPEND WA_FIELDCAT TO IT_FIELDCAT.
CLEAR WA_FIELDCAT.
WA_FIELDCAT-COL_POS = 2.
WA_FIELDCAT-FIELDNAME = 'COUNT'.
WA_FIELDCAT-REPTEXT_DDIC = 'Number of records'.
WA_FIELDCAT-OUTPUTLEN = 35.
WA_FIELDCAT-EMPHASIZE = 'C100'.
WA_FIELDCAT-TABNAME = 'IT_DISPLAY'.
APPEND WA_FIELDCAT TO IT_FIELDCAT.
CLEAR WA_FIELDCAT.
*& Call functin for grid dispaly
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
I_CALLBACK_PROGRAM = I_REPID
I_GRID_TITLE = I_TITLE_GRID
IT_FIELDCAT = IT_FIELDCAT
TABLES
T_OUTTAB = IT_DISPLAY[].
IF sy-subrc <> 0.
WRITE : 'THERE IS AN ERROR'.
ENDIF.
ENDIF.
"***************************************************end************************************************************
Your comments and suggestions are most welcome !
