Skip to Content

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.
TYPEPOOLS: SLIS.

*& For selection screen
SELECTIONSCREEN: BEGIN OF BLOCK B02 WITH FRAME TITLE TEXT002.

  PARAMETERS: P_OBJ TYPE C LENGTH 20.

SELECTIONSCREEN: END OF BLOCK B02.

*& data decleration
TYPES : BEGIN OF TY_TABLE,
           TABNAME      TYPE    DD02LTABNAME,
           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.

DATAIT_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    SYREPID,
       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_TAB3COUNT FROM (WA_TAB2TABNAME).
    WA_TAB3TABNAME = WA_TAB2TABNAME.

  APPEND WA_TAB3 TO IT_TAB3.

ENDLOOP.

*& For grid display
LOOP AT IT_TAB3.
        WA_DISPLAYTABNAME = IT_TAB3TABNAME.
        WA_DISPLAYCOUNT = IT_TAB3COUNT.
        APPEND WA_DISPLAY TO IT_DISPLAY.

  ENDLOOP.

* Assigning column names and other attributes for the grid
      WA_FIELDCATCOL_POS = 1.
      WA_FIELDCATFIELDNAME = ‘TABNAME’.
      WA_FIELDCATREPTEXT_DDIC = ‘Table name’.
      WA_FIELDCATOUTPUTLEN = 50.
      WA_FIELDCATEMPHASIZE = ‘C300’.
      WA_FIELDCATTABNAME = ‘IT_DISPLAY’.
      APPEND WA_FIELDCAT TO IT_FIELDCAT.
      CLEAR WA_FIELDCAT.

      WA_FIELDCATCOL_POS = 2.
      WA_FIELDCATFIELDNAME = ‘COUNT’.
      WA_FIELDCATREPTEXT_DDIC = ‘Number of records’.
      WA_FIELDCATOUTPUTLEN = 35.
      WA_FIELDCATEMPHASIZE = ‘C100’.
      WA_FIELDCATTABNAME = ‘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 sysubrc <> 0.
       WRITE : ‘THERE IS AN ERROR’.
      ENDIF.

ENDIF.

“***************************************************end************************************************************

Your comments and suggestions are most welcome !

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Michelle Crapo

    I always love an interesting new program.  You might try ALV OO.  It’s actually easier to use.  Interesting way to do a quick search for tables with data.   I honestly would have been sick of doing SE16 table count after the first 10.

    How long does it take to run?   Have you thought about building on to it with a table key comparison?

    Great blog!

    Michelle

    (0) 
    1. Umran Syed Post author

      Halo Michelle,

      Thank you for your comments.

      It will take not more than a few seconds.

      Also interesting is you can just enter the name of any info object, cube, dso or any table etc.(Also a few letters of the technical name of any of these objects is sufficient as an input). It will fetch all the tables in the system with the matching kew words you entered as a wild card search, and will display all the relevant table list and the number of records present in them.

      Will think about your idea for table key comparison.

      Bbye.

      (0) 

Leave a Reply