Skip to Content
Technical Articles
Author's profile photo arghadip kar
arghadip kar
August 22, 2020 6 minute read

How to find the Table that stores multiple Field values? That’s what ABAPER and Functional does :) in WRICEFF development

Introduction

After working with SAP for more than 15 years and travelling across the Globe for SAP Projects one issue I always use to have how can I keep track of all the SAP Tables associated with different Modules. If you are working as a SAP ABAP Consultant you need to keep the name of tables in your Fingertips. Sometimes this you know from other Experts and sometimes you have to dig it out.

After exploring I found a very easy way to know the Tables that contains all the four fields or Data Element in the same table and has data in it.

SAP is just like an ocean and every droplet is like one SAP Table. This is one of the first program I create in any Project.

I have one request the .1% time you saved while implementing this Blog, help someone in need by doing Service. It can be as simple as playing with a Kid also.

 

This solution only does Table. I will do another blog  for Views also.

Solution

Go to SE38-> Create a Program and Paste this Code.

Here is the Execution. Just to let you know I have Excluded the Table Beginning with ‘/’. If you want to add it just tweak the code.

In the Below example I am looking for tables with these 2 fields/Data Element. EBELN and EBELP

Wala here is the Result

 

Lets be adventurous and try with 4 fields

 

Before this I never knew knew EKPO has all the above 4 Fields/Data Element. Wala we got the result.

Below is the Code that probably can fetch some serious Fun Time to learn something more.

TABLES: DD03L,
        BOOLE.

DATA:DREF TYPE REF TO DATA.
FIELD-SYMBOLS:<TABLE> TYPE STANDARD TABLE.
TYPE-POOLS: SLIS, ICON.

* Internal Tables
TYPES: BEGIN OF T_IALV,
         TABNAME TYPE DD03L-TABNAME,
         FIELD1  TYPE DD03L-FIELDNAME,
         FIELD2  TYPE DD03L-FIELDNAME,
         FIELD3  TYPE DD03L-FIELDNAME,
         FIELD4  TYPE DD03L-FIELDNAME,
       END OF T_IALV .

DATA: IALV   TYPE STANDARD TABLE OF T_IALV,
      WA_ALV TYPE T_IALV.

DATA: ALV_CONTAINER TYPE REF TO CL_GUI_CUSTOM_CONTAINER,
      ALV_GRID      TYPE REF TO CL_GUI_ALV_GRID,
      OK_CODE       LIKE SY-UCOMM,
      FIELDCAT      TYPE SLIS_T_FIELDCAT_ALV WITH HEADER LINE.
SELECT-OPTIONS:S_FIELD1 FOR DD03L-FIELDNAME NO-EXTENSION,
S_FIELD2 FOR DD03L-FIELDNAME NO-EXTENSION,
S_FIELD3 FOR DD03L-FIELDNAME NO-EXTENSION,
S_FIELD4 FOR DD03L-FIELDNAME NO-EXTENSION,
S_EXC FOR BOOLE-BOOLE.

FIELD-SYMBOLS:<FS-TAB13> TYPE MANDT.
DATA:WA_DD1      TYPE DD03L,
     WA_DD2      TYPE DD03L,
     WA_DD3      TYPE DD03L,
     WA_DD4      TYPE DD03L,
     LV_TAB      TYPE DD03L-TABNAME,
     WA_DD_FINAL TYPE DD03L.
IF S_EXC-LOW IS NOT INITIAL.
  LV_TAB = '/%'.
ENDIF.
IF S_FIELD1-LOW IS NOT INITIAL.
  SELECT C~TABNAME
    FROM ( ( DD03L AS C
         INNER JOIN DD02V AS P ON P~TABNAME  = C~TABNAME
                              AND P~TABCLASS = 'TRANSP')
*                              AND p~cityto   = @cityto )
         INNER JOIN DD09L AS F ON F~TABNAME = P~TABNAME )
    WHERE ( ( C~ROLLNAME EQ @S_FIELD1-LOW )
    OR ( C~FIELDNAME EQ @S_FIELD1-LOW  ) )
    AND P~TABNAME NOT LIKE @LV_TAB
       INTO TABLE @DATA(ITAB13).
ENDIF.
IF S_FIELD2-LOW IS NOT INITIAL.
  SELECT C~TABNAME
  FROM ( ( DD03L AS C
       INNER JOIN DD02V AS P ON P~TABNAME  = C~TABNAME
                            AND P~TABCLASS = 'TRANSP')
*                              AND p~cityto   = @cityto )
       INNER JOIN DD09L AS F ON F~TABNAME = P~TABNAME )
  WHERE ( ( C~ROLLNAME EQ @S_FIELD2-LOW )
  OR ( C~FIELDNAME EQ @S_FIELD2-LOW  )    )
  AND P~TABNAME NOT LIKE @LV_TAB
     INTO TABLE @DATA(ITAB14).
ENDIF.
IF S_FIELD3-LOW IS NOT INITIAL.
  SELECT C~TABNAME
  FROM ( ( DD03L AS C
       INNER JOIN DD02V AS P ON P~TABNAME  = C~TABNAME
                            AND P~TABCLASS = 'TRANSP')
       INNER JOIN DD09L AS F ON F~TABNAME = P~TABNAME )
  WHERE ( ( C~ROLLNAME EQ @S_FIELD3-LOW )

  OR ( C~FIELDNAME EQ @S_FIELD3-LOW  )    )
  AND P~TABNAME NOT LIKE @LV_TAB
     INTO TABLE @DATA(ITAB15).
ENDIF.
IF S_FIELD4-LOW IS NOT INITIAL.
  SELECT C~TABNAME
FROM ( ( DD03L AS C
     INNER JOIN DD02V AS P ON P~TABNAME  = C~TABNAME
                          AND P~TABCLASS = 'TRANSP')
*                              AND p~cityto   = @cityto )
     INNER JOIN DD09L AS F ON F~TABNAME = P~TABNAME )
WHERE ( ( C~ROLLNAME EQ @S_FIELD4-LOW )
OR ( C~FIELDNAME EQ @S_FIELD4-LOW  )
)
AND P~TABNAME NOT LIKE @LV_TAB
*                                AND f~connid = p~connid )
*       ORDER BY c~carrname, p~connid, f~fldate
   INTO TABLE @DATA(ITAB16).
ENDIF.
SORT ITAB13 BY TABNAME.
DELETE ADJACENT DUPLICATES FROM ITAB13 COMPARING TABNAME..
SORT ITAB14 BY TABNAME.
DELETE ADJACENT DUPLICATES FROM ITAB14 COMPARING TABNAME..
SORT ITAB15 BY TABNAME.
DELETE ADJACENT DUPLICATES FROM ITAB15 COMPARING TABNAME..
SORT ITAB16 BY TABNAME.
DELETE ADJACENT DUPLICATES FROM ITAB16 COMPARING TABNAME.
IF S_FIELD1-LOW IS NOT INITIAL.

  LOOP AT ITAB13 INTO DATA(WA_TAB133).
    IF ITAB14[] IS NOT INITIAL.
      READ TABLE ITAB14  INTO DATA(WA_TAB134) WITH KEY TABNAME = WA_TAB133-TABNAME.
      IF SY-SUBRC = 0.
        CREATE DATA DREF TYPE TABLE OF (WA_TAB134-TABNAME).
        IF ITAB15[] IS NOT INITIAL.
          READ TABLE ITAB15  INTO DATA(WA_TAB135) WITH KEY TABNAME = WA_TAB133-TABNAME.
          IF SY-SUBRC = 0.
            IF ITAB16[] IS NOT INITIAL.
              READ TABLE ITAB16  INTO DATA(WA_TAB136) WITH KEY TABNAME = WA_TAB133-TABNAME.
              IF SY-SUBRC = 0.
                ASSIGN DREF->* TO <TABLE>.
                SELECT  *
              FROM (WA_TAB134-TABNAME)
                  INTO TABLE @<TABLE>.
                IF <TABLE> IS NOT INITIAL.

                  WA_ALV-TABNAME =  WA_TAB134-TABNAME.
                  WA_ALV-FIELD1 =  S_FIELD1-LOW.
                  WA_ALV-FIELD2 =  S_FIELD2-LOW.
                  WA_ALV-FIELD3 =  S_FIELD3-LOW.
                  WA_ALV-FIELD4 =  S_FIELD4-LOW.
                  APPEND WA_ALV TO IALV.
                  CLEAR WA_ALV.
*      exit.
                ENDIF.
              ELSE.

                ASSIGN DREF->* TO <TABLE>.
                SELECT  *
              FROM (WA_TAB134-TABNAME)
                  INTO TABLE @<TABLE>.
                IF <TABLE> IS NOT INITIAL.

                  WA_ALV-TABNAME =  WA_TAB134-TABNAME.
                  WA_ALV-FIELD1 =  S_FIELD1-LOW.
                  WA_ALV-FIELD2 =  S_FIELD2-LOW.
                  WA_ALV-FIELD3 =  S_FIELD3-LOW.
*                  WA_ALV-FIELD4 =  S_FIELD4-LOW.
                  APPEND WA_ALV TO IALV.
                  CLEAR WA_ALV.
*      exit.
                ENDIF.
              ENDIF.
            ELSE.

              ASSIGN DREF->* TO <TABLE>.
              SELECT  *
            FROM (WA_TAB134-TABNAME)
                INTO TABLE @<TABLE>.
              IF <TABLE> IS NOT INITIAL.

                WA_ALV-TABNAME =  WA_TAB134-TABNAME.
                WA_ALV-FIELD1 =  S_FIELD1-LOW.
                WA_ALV-FIELD2 =  S_FIELD2-LOW.
                WA_ALV-FIELD3 =  S_FIELD3-LOW.

                APPEND WA_ALV TO IALV.
                CLEAR WA_ALV.

              ENDIF.
            ENDIF.
          ELSE.
            ASSIGN DREF->* TO <TABLE>.
            SELECT  *
          FROM (WA_TAB134-TABNAME)
              INTO TABLE @<TABLE>.
            IF <TABLE> IS NOT INITIAL.

              WA_ALV-TABNAME =  WA_TAB134-TABNAME.
              WA_ALV-FIELD1 =  S_FIELD1-LOW.
              WA_ALV-FIELD2 =  S_FIELD2-LOW.

              APPEND WA_ALV TO IALV.
              CLEAR WA_ALV.

            ENDIF.
          ENDIF.


        ELSE.
          ASSIGN DREF->* TO <TABLE>.
          SELECT  *
        FROM (WA_TAB134-TABNAME)
            INTO TABLE @<TABLE>.
          IF <TABLE> IS NOT INITIAL.
            WA_ALV-TABNAME =  WA_TAB134-TABNAME.
            WA_ALV-FIELD1 =  S_FIELD1-LOW.
            WA_ALV-FIELD2 =  S_FIELD2-LOW.

            APPEND WA_ALV TO IALV.
            CLEAR WA_TAB134.

          ENDIF.
        ENDIF.

      ENDIF.
    ENDIF.
*  endif.
  ENDLOOP.


ENDIF.


*  Populate Field Catalog
PERFORM GET_FIELDCATALOG.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
  EXPORTING
    I_CALLBACK_PROGRAM = SY-REPID
*   is_layout          = w_layout
    IT_FIELDCAT        = FIELDCAT[]
*   it_events          = i_events
  TABLES
    T_OUTTAB           = IALV
  EXCEPTIONS
    PROGRAM_ERROR      = 1
    OTHERS             = 2.
IF SY-SUBRC <> 0.
* Implement suitable error handling here
ENDIF.


************************************************************************
*      Form  Get_Fieldcatalog - Set Up Columns/Headers
************************************************************************
FORM GET_FIELDCATALOG.

  CLEAR: FIELDCAT.
  FIELDCAT-SELTEXT_M    = 'Tablename'.
  FIELDCAT-FIELDNAME  = 'TABNAME'.
  FIELDCAT-OUTPUTLEN  = '36'.
  APPEND FIELDCAT TO FIELDCAT.

  CLEAR: FIELDCAT.
  FIELDCAT-SELTEXT_M   = 'Field1'.
  FIELDCAT-FIELDNAME  = 'FIELD1'.
  FIELDCAT-OUTPUTLEN  = '12'.
  APPEND FIELDCAT TO FIELDCAT.

  CLEAR: FIELDCAT.
  FIELDCAT-SELTEXT_M   = 'Field2'.
  FIELDCAT-FIELDNAME  = 'FIELD2'.
  FIELDCAT-OUTPUTLEN  = '12'.
  APPEND FIELDCAT TO FIELDCAT.

  CLEAR: FIELDCAT.
  FIELDCAT-SELTEXT_M   = 'Field3'.
  FIELDCAT-FIELDNAME  = 'FIELD3'.
  FIELDCAT-OUTPUTLEN  = '12'.
  APPEND FIELDCAT TO FIELDCAT.

  CLEAR: FIELDCAT.
  FIELDCAT-SELTEXT_M    = 'Field4'.
  FIELDCAT-FIELDNAME  = 'FIELD4'.
  FIELDCAT-OUTPUTLEN  = '12'.
  APPEND FIELDCAT TO FIELDCAT.

ENDFORM.

Here is the Selection Screen Parameter label

 

Conclusion

This will work for both SAP ECC and SAP S/4 HANA

 

Below is the video version

 

Assigned Tags

      17 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Sagar Dhange
      Sagar Dhange

      This Blog is really helpful thanks for bringing this up.

      Surly will use this technique from now onwords.

      Author's profile photo arghadip kar
      arghadip kar
      Blog Post Author

      Thanks a Lot

      Author's profile photo Sayan Dey
      Sayan Dey

      Great blog there! Will be very helpful for the BW Consultants as well. Thanks, really!

      Author's profile photo Gaurav Karkara
      Gaurav Karkara

      Hi Arghadip,

      I would like to propose an alternative that can do similar function. SE84 -> ABAP Dictionary -> Fields -> Table Fields -> Enter fields in field names and execute.

      You will get the list of tables. You can identify the ones which has both fields.

      Gaurav

      Author's profile photo arghadip kar
      arghadip kar
      Blog Post Author

      Nice one but it will propose tables that may not have any entries hence it might not be solving the purpose

      Author's profile photo Jozsef Szikszai
      Jozsef Szikszai

      Actually SE84 is not an alternative, but clearly better, as we are not limited to four fields.

      Author's profile photo gowri pujitha kapu
      gowri pujitha kapu

      Awesome report ..

      Author's profile photo Enno Wulff
      Enno Wulff

      Hey arghadip kar

      It really is sometimes a task to search tables for having exactly these fields. It's annoying and cumbersome to do this with standard transactions.

      Work-around with SE16n

      With the help of SE16n you can switch on the option "Determine Number Of Lines Dsiplayed" in the Technical Settings.

      In the output list for table DD03L you can sub total field "tablename" and set the drilldown level to total level.

      and then look out for entries which correspond to the total of selected fieldnames:

      But you cannot select the table class (Transparent table or structure) which is not really helpful.

      Your report

      I am not sure if your program works correctly.

      In my system table EKPO has no field BELNR. Therefor I have table BSAK which has all four fields but it's not listed in your results.

      You do not only look for FIELDNAMES but also for ROLLNAMES having the same name. This is a good idea, because the names sometimes differ.

      I would prefer to decide on my own, if FIELD or ROLLNAME should be respected in the search.

      My 1st solution

      I wanted to present you a shorter and more efficient solution:

      DATA gv_tablename TYPE tabname.
DATA gv_tableclass TYPE tabclass.
DATA gv_fieldname TYPE fieldname.
DATA gv_rollname TYPE rollname.

SELECT-OPTIONS s_field1 FOR gv_fieldname.
SELECT-OPTIONS s_rolln1 FOR gv_fieldname.
SELECT-OPTIONS s_field2 FOR gv_fieldname.
SELECT-OPTIONS s_rolln2 FOR gv_fieldname.
SELECT-OPTIONS s_field3 FOR gv_fieldname.
SELECT-OPTIONS s_rolln3 FOR gv_fieldname.
SELECT-OPTIONS s_field4 FOR gv_fieldname.
SELECT-OPTIONS s_rolln4 FOR gv_fieldname.

SELECT-OPTIONS s_tbnam FOR gv_tablename.
SELECT-OPTIONS s_tbcls FOR gv_tableclass DEFAULT 'TRANSP'.

START-OF-SELECTION.


  SELECT DISTINCT
     dd02l~tabname,
     f1~fieldname as field_1, f1~rollname as rollname_1,
     f2~fieldname as field_2, f2~rollname as rollname_2,
     f3~fieldname as field_3, f3~rollname as rollname_3,
     f4~fieldname as field_4, f4~rollname as rollname_4
    FROM dd02l
   INNER JOIN dd03l AS f1 ON dd02l~tabname = f1~tabname
   INNER JOIN dd03l AS f2 ON dd02l~tabname = f2~tabname
   INNER JOIN dd03l AS f3 ON dd02l~tabname = f3~tabname
   INNER JOIN dd03l AS f4 ON dd02l~tabname = f4~tabname
    INTO TABLE @DATA(result)
   WHERE dd02l~tabname  IN @s_tbnam
     AND dd02l~tabclass IN @s_tbcls
     AND f1~fieldname IN @s_field1 AND f1~rollname IN @s_rolln1
     AND f2~fieldname IN @s_field2 AND f2~rollname IN @s_rolln2
     AND f3~fieldname IN @s_field3 AND f3~rollname IN @s_rolln3
     AND f4~fieldname IN @s_field4 AND f4~rollname IN @s_rolln4
     ORDER BY dd02l~tabname.

  cl_demo_output=>display_data( result ).

      Unfortunately this solution only works with exactly 4 fieldnames/ rollnames. I let this coding exist because it documents the dynamically created SELECT in the next solution.

      My 2nd Solution

      DATA gs_dd02l  TYPE dd02l.
DATA gs_dd03l  TYPE dd03l.

SELECT-OPTIONS s_field1 FOR gs_dd03l-fieldname.
SELECT-OPTIONS s_rolln1 FOR gs_dd03l-fieldname.
SELECT-OPTIONS s_field2 FOR gs_dd03l-fieldname.
SELECT-OPTIONS s_rolln2 FOR gs_dd03l-fieldname.
SELECT-OPTIONS s_field3 FOR gs_dd03l-fieldname.
SELECT-OPTIONS s_rolln3 FOR gs_dd03l-fieldname.
SELECT-OPTIONS s_field4 FOR gs_dd03l-fieldname.
SELECT-OPTIONS s_rolln4 FOR gs_dd03l-fieldname.

SELECT-OPTIONS s_tbnam FOR gs_dd02l-tabname.
SELECT-OPTIONS s_tbcls FOR gs_dd02l-tabclass DEFAULT 'TRANSP'.

DEFINE _dynsel_for_field.
  IF s_field&1[] IS NOT INITIAL OR s_rolln&1[] IS NOT INITIAL.
    APPEND `f&1~fieldname as field_&1, f&1~rollname as rollname_&1,` TO fieldnames.
    APPEND `INNER JOIN dd03l AS f&1 ON dd02l~tabname = f&1~tabname` TO source.
    if where is NOT INITIAL.
      append 'AND' to where.
    endif.
    APPEND |f&1~fieldname IN @s_field&1 AND f&1~rollname IN @s_rolln&1| TO where.
  ENDIF.

END-OF-DEFINITION.

START-OF-SELECTION.

  IF s_field1[] IS INITIAL
  AND s_rolln1[] IS INITIAL.
    MESSAGE 'first field/ rollname is obligatory!' TYPE 'I'.
    STOP.
  ENDIF.

  TYPES: BEGIN OF _result,
           tabname    TYPE tabname,
           field_1    TYPE fieldname,
           rollname_1 TYPE rollname,
           field_2    TYPE fieldname,
           rollname_2 TYPE rollname,
           field_3    TYPE fieldname,
           rollname_3 TYPE rollname,
           field_4    TYPE fieldname,
           rollname_4 TYPE rollname,
         END OF _result.
  DATA result TYPE STANDARD TABLE OF _result.

  DATA(where) = VALUE string_table( ).
  DATA(fieldnames) = VALUE string_table( ).
  DATA(source) = VALUE string_table( ( `dd02l` ) ).

  _dynsel_for_field 1.
  _dynsel_for_field 2.
  _dynsel_for_field 3.
  _dynsel_for_field 4.

  APPEND `dd02l~tabname` TO fieldnames.

  SELECT DISTINCT (fieldnames)
    FROM (source)
    INTO CORRESPONDING FIELDS OF TABLE @result
   WHERE dd02l~tabname  IN @s_tbnam
     AND dd02l~tabclass IN @s_tbcls
     AND (where)
     ORDER BY dd02l~tabname.

  cl_demo_output=>display_data( result ).

      so my solution is not really shorter but can be easily expanded to 5, 6 and more fields to search for. Plus: it's maybe more obvious what is happening here.

      side effects

      This report also reveals multiple fieldnames having the same rollname, like table COFP:

      Cheers
      ~Enno

      Author's profile photo Mahmood Shakir Hammood
      Mahmood Shakir Hammood

      Hello Enno,

       

      I am a beginner in ABAP, I want to make the same what you made but alittel bit deffirent. I need iinput field and value help when I click on it I get all tables from my system. When I choose some table and run the program I have to see the content of table and also be able to edit the table. Test Data Generator. Maybe you have a blog.

      Author's profile photo arghadip kar
      arghadip kar
      Blog Post Author

      Awesome

       

      Please Create it in a blog so others can benefit.

      Again great one

      Author's profile photo Nivardo Edwin Tahua Quijano
      Nivardo Edwin Tahua Quijano

      danke für das Teilen!!

      Author's profile photo Glenn Chamuel
      Glenn Chamuel

      Excellent blog! Thank you. It is very helpful.

      Have you posted your follow-up for Views?: “This solution only does Table. I will do another blog  for Views also.”

      A blog for views will be very helpful for reporting. I’m looking forward to it.

      Author's profile photo arghadip kar
      arghadip kar
      Blog Post Author

      Glad you liked it.

       

      I will publish soon the one for the Views. Probably by this week.

       

      Thanks

      Arghadip

      Author's profile photo arghadip kar
      arghadip kar
      Blog Post Author

      Here is the next blog you were asking for

       

      https://blogs.sap.com/2020/11/26/how-to-find-the-views-cds-views-that-stores-multiple-field-values-thats-what-abaper-and-functional-does-in-wriceff-development/

       

      Author's profile photo Renata Reich
      Renata Reich

      Hi Arghadip,

      this is an interesting approach, thank you for sharing this with us.

      I would like to propose one more alternative which I use on regular basis for such kind of searches and many more.

      Luckily we have Ludwig Stockbauer-Muhr  in our community and he has published last year his

      plugin ABAP Search and Analysis Tool for eclipse, which allows such kind of search queries in a very

      simple manner:

       

      The same search can be done also for CDS Views just by changing the Type to "CDS View" instead of "Database Table/View"

      The search is of course not limited to 4 fields, but you can enter as many as you need.

      Author's profile photo arghadip kar
      arghadip kar
      Blog Post Author

      Wow thats great. I think you can also do this using SE16T Tcode also but it is a little tricky

       

      https://youtu.be/oYqtku2g8ts

      Author's profile photo Mahmood Shakir Hammood
      Mahmood Shakir Hammood

      Hello arghadip kar,

       

      I am a beginner in ABAP, I want to make the same what you made but alittel bit deffirent. I need iinput field and value help when I click on it I get all tables from my system. When I choose some table and run the program I have to see the content of table and also be able to edit the table. Test Data Generator. Maybe you have a blog.