Search for a value in a field in every table that the field appears.

Jimbo's picture

searching mountain goatThe ability to find every occurrence of a master data object in every SAP table is a great way for programmers to track down how those are used, but there are many unique values in SAP besides KUNNR, LIFNR and MATNR. How convenient would it be to have a Where used button for any value in SAP that lists every occurrence in any table?

It's possible to accomplish this with just a short snippet of code and a little effort. Start by setting up some parameters in the GLOBAL_DATA section of a new LSMW object (or report in SE38). Putting the parameters in a block with some comments makes it prettier and easier to use.

selection-screen begin of block ctable with frame title btitle3.
  selection-screen begin of line.
    selection-screen comment 1(12) commj1.
    PARAMETERS: p_FIELDV like DD03L-Fieldname.
  selection-screen end of line.
  selection-screen begin of line.
    selection-screen comment 1(13) commj2.
    PARAMETERS: p_SEARCH(20) type c.
    selection-screen comment 37(20) commj3.
  selection-screen end of line.
selection-screen end of block ctable.
btitle3 = 'Search for table based on field name and value.'.
commj1 = 'Field name:'.
commj2 = 'Search value:'.
commj3 = '(case sensetive)'.

* Use on the necessary fields; loading 8 million whole records 
* into memory can cause program to crash.
types: begin of t_DD03L,
         TABNAME like DD03L-TABNAME,
       end of t_DD03L.
data: it_DD03L type standard table of t_DD03L initial size 0,
      lDD03L type t_DD03L, lDD02L like DD02L, nRecordCount type i.

data: oref type ref to cx_root.

data: where_tab(30) occurs 1 with header line,
  where_clause(30) type c.

In the BEGIN_OF_PROCESSING section, add some code to first populate the internal table with the list of fields. This will speed up the processing later.

 into corresponding fields of lDD03L
 order by TABNAME.
  append lDD03L to it_DD03L.

Now the system can loop through all the tables that have the field and search that field for the desired value. This search is case sensitive, so if the value is normally in all capital letters then the value will not be found if the search value is lower case.

The try→catch→endtry routine is there to catch tables that do not have a field advertised in DD03L. This seems like something that would never happen, but it absolutely does.

The where clause in the select is passed to the select in the form of an internal table of character values. Each comparison is passed as its own line in the table, but this where clause requires just one comparison.

if not p_FIELDV is initial.
  clear where_tab.
  concatenate p_FIELDV '=' p_SEARCH into where_clause
   separated by space.
  concatenate '''' p_SEARCH '''' into where_clause.
  concatenate p_FIELDV '=' where_clause into where_clause
   separated by space.
  append where_clause to where_tab.

  loop at it_DD03L into lDD03L where FIELDNAME eq p_FIELDV.
    perform ShowProgress using p_SEARCH lDD03L-TABNAME.
      select * from DD02L into lDD02L where TABNAME eq lDD03L-TABNAME
       and TABCLASS eq 'TRANSP' and AS4LOCAL ne 'L'.
        select count(*) from (lDD03L-TABNAME) into nRecordCount
         where (where_tab).

        perform UpdateTableList using P_SEARCH
         lDD03L-TABNAME nRecordCount.
    catch cx_root INTO oref.


loop at it_Table into wa_Table.
  write: / wa_Table-Object, 'appears in', wa_Table-TABNAME,
   wa_Table-RecordCount, 'times.', wa_Table-TableDesc.

Phew!Since this LSMW object will most likely be used solely as a report and not to actually load data, the transaction is skipped at the BEGIN_OF_TRANSACTION. This report was built on an LSMW of type Standard Batch/Direct Input using 001 Long Texts (does not require logical file on the server) along with a dummy text file.

Finally, this form is added to the FORM_ROUTINES to collect the occurrences of the value in every table. Each occurrence is appened to the it_Table internal table along with the number of hits in that table and the description of the table.

form ShowProgress using lvSearch lvTableName.
  data: lvMessage type string.

  concatenate 'Searching for' lvSearch 'in' lvTableName '. . .'
   into lvMessage separated by space.

      TEXT = lvMessage
      others = 1.

form UpdateTableList using lvObject lvTableName lvRecordCount.
  if lvRecordCount gt 0.  "Found!
    add lvRecordCount to nTotalRecords.
    wa_Table-Object = lvObject.
    wa_Table-TABNAME = lvTableName.
    wa_Table-RecordCount = lvRecordCount.
    select single DDTEXT from DD02T into wa_TABLE-TableDesc
     where TABNAME eq lvTableName.
    select single DDTEXT from DD02T into wa_TABLE-TableDesc
     where TABNAME eq lvTableName and DDLANGUAGE eq 'E'.
    append wa_TABLE to it_TABLE.


Naturally, programming doesn't occur in a vacuum and those producing certainly deserve to be credited. Special thanks to Nicolai Marco for this brilliant explanation of dynamic queries.

searching mountain goat

Programming Language: