Program to find number of records inside any list of tables

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 !