Find all occurances of master data objects
Starting off as a developer in ABAP can be a daunting task due to the complexity of the system. In addition to the countless programs written and buried within SAP there are tens of thousands of native tables--many of them abandoned and empty. This makes it difficult to find where a single piece of information resides in a system nearly too complex to use, much less develop software for.
Knowing where a specific value occurs in the SAP database structure can greatly ease the development of software to validate and change these values. By checking the values in tables it is possible to know if the value needs to be changed or is already correct as it is. This saves time and effort, reduces the strain on a busy server and simplifies management of the system.
This tool lists the occurrence of master data objects in any table and can be used to find other values in a table based the names of fields in the table. It requires a dummy text file, but is very simple to import and use.
To use the tool type in the number of the master data in the appropriate field. To check link tables as well, be sure to tick the Perform Deep Search checkbox. This will find information about the master data object in tables that don't use the usual master data field names MATNR, KUNNR and LIFNR. The Object Navigator (transaction code SE80) has a tool to find tables where a field name is used, but too often the need to find a table where two field names both exist is too great and there is no time to do cross lookups on hundreds of tables. This tool finds all the tables where both field names are included and notes if the master data object occurs in that table.
Add the following lines to the GLOBAL_DATA section. This defines the variables that we will be using.
selection-screen begin of block bmaster with frame title btitle1.
selection-screen begin of line.
selection-screen comment 1(10) comm1.
PARAMETERS: p_MATNR like MARA-MATNR.
selection-screen end of line.
selection-screen begin of line.
selection-screen comment 1(10) comm2.
PARAMETERS: p_KUNNR like KNA1-KUNNR.
selection-screen end of line.
selection-screen begin of line.
selection-screen comment 1(10) comm3.
PARAMETERS: p_LIFNR like LFA1-LIFNR.
selection-screen end of line.
selection-screen begin of line.
PARAMETERS: p_Deep as checkbox default ' '.
selection-screen comment 3(65) commdeep.
selection-screen end of line.
selection-screen end of block bmaster.
selection-screen begin of block btable with frame title btitle2.
selection-screen begin of line.
selection-screen comment 1(10) commf1.
PARAMETERS: p_FIELD1 like DD03L-Fieldname.
selection-screen end of line.
selection-screen begin of line.
selection-screen comment 1(10) commf2.
PARAMETERS: p_FIELD2 like DD03L-Fieldname.
selection-screen end of line.
selection-screen begin of line.
parameters p_TRANSP as checkbox default 'X'.
selection-screen comment 3(60) commtr.
selection-screen end of line.
selection-screen begin of line.
parameters p_Ignore as checkbox default 'X'.
selection-screen comment 3(60) commie.
selection-screen end of line.
selection-screen end of block btable.
initialization.
btitle1 = 'Search for master data (Where used).'.
btitle2 = 'Search for table based on field names.'.
comm1 = 'Material:'.
comm2 = 'Customer:'.
comm3 = 'Vendor:'.
commdeep = 'Perform deep search (searches link tables--slow!).'.
commf1 = 'Fieldname 1:'.
commf2 = 'Fieldname 2:'.
commtr = 'List transparent tables only'.
commie = 'Ignore empty tables.'.
data: lDD02L like DD02L, lDD03L like DD03L, lDD03L2 like DD03L,
lDD02T like DD02T, nRecordCount type i, lTableDesc(40) type c.
* Following stores data from first search to enrich second search.
types: begin of t_Table,
TABNAME like DD02L-TABNAME,
Object(18) type c,
RecordCount type i,
TableDesc like DD02T-DDTEXT,
end of t_Table.
data: it_Table type standard table of t_Table initial size 0,
wa_Table type t_Table.
In the BEGIN_OF_PROCESSING section add this code to produce the report. This report takes several minutes on a slow system or an older system with a great deal of data.
if p_MATNR ne ''.
translate p_MATNR to UPPER CASE.
if p_MATNR co ' 1234567890'.
shift p_MATNR right deleting trailing space.
overlay p_MATNR with '000000000000000000'.
endif.
select * from DD03L into lDD03L where FIELDNAME eq 'MATNR'
order by TABNAME.
perform ShowProgress using p_MATNR 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 MATNR eq p_MATNR.
perform UpdateTableList using p_MATNR lDD03L-TABNAME nRecordCount.
endselect.
endselect.
if p_Deep eq 'X'. "Deep search it!
perform DeepSearch using p_MATNR.
endif.
endif.
if p_KUNNR ne ''.
translate p_KUNNR to UPPER CASE.
if p_KUNNR co ' 1234567890'.
shift p_KUNNR right deleting trailing space.
overlay p_KUNNR with '0000000000'.
endif.
select * from DD03L into lDD03L where FIELDNAME eq 'KUNNR'
order by TABNAME.
perform ShowProgress using p_KUNNR 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 KUNNR eq p_KUNNR.
perform UpdateTableList using p_KUNNR lDD03L-TABNAME nRecordCount.
endselect.
endselect.
if p_Deep eq 'X'. "Deep search it!
perform DeepSearch using p_KUNNR.
endif.
endif.
if p_LIFNR ne ''.
translate p_LIFNR to UPPER CASE.
if p_LIFNR co ' 1234567890'.
shift p_LIFNR right deleting trailing space.
overlay p_LIFNR with '0000000000'.
endif.
select * from DD03L into lDD03L where FIELDNAME eq 'LIFNR'
order by TABNAME.
perform ShowProgress using p_LIFNR 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 LIFNR eq p_LIFNR.
perform UpdateTableList using p_LIFNR lDD03L-TABNAME nRecordCount.
endselect.
endselect.
if p_Deep eq 'X'. "Deep search it!
perform DeepSearch using p_LIFNR.
endif.
endif.
loop at it_Table into wa_Table.
write: / wa_Table-Object, 'appears in', wa_Table-TABNAME,
wa_Table-RecordCount, 'times.', wa_Table-TableDesc.
endloop.
if p_FIELD1 ne ''.
translate p_FIELD1 to UPPER CASE.
select * from DD03L into lDD03L where FIELDNAME eq p_FIELD1.
select * from DD02L into lDD02L
where TABNAME eq lDD03L-TABNAME and AS4LOCAL ne 'L'.
select single * from DD02T into lDD02T
where TABNAME eq lDD03L-TABNAME.
select single * from DD02T into lDD02T
where TABNAME eq lDD03L-TABNAME and DDLANGUAGE eq 'E'.
if lDD02L-TABCLASS eq 'TRANSP' or p_TRANSP ne 'X'.
if p_FIELD2 eq ''.
select count(*) from (lDD03L-TABNAME) into nRecordCount.
if nRecordCount gt 0 or p_Ignore ne 'X'.
write: / p_FIELD1, 'used in', lDD03L-TABNAME color 7 inverse,
lDD02T-DDTEXT, 'Records:', nRecordCount color 5.
endif.
else.
translate p_FIELD2 to UPPER CASE.
select * from DD03L into lDD03L2
where TABNAME eq lDD03L-TABNAME and FIELDNAME eq p_FIELD2.
select count(*) from (lDD03L-TABNAME) into nRecordCount.
if nRecordCount gt 0 or p_Ignore ne 'X'.
write: / p_FIELD1, 'and', p_FIELD2, 'used in',
lDD03L-TABNAME, lDD02T-DDTEXT, 'Records:', nRecordCount.
loop at it_Table into wa_table
where TABNAME eq lDD03L-TABNAME.
write: '(listed above)' color col_positive.
endloop.
endif.
endselect.
endif.
endif.
endselect.
endselect.
endif.
In the BEGIN_OF_TRANSACTION section, be sure to add this line of code to prevent the LSMW object from creating any transactions.
skip_transaction.
Finally, add these forms to the FORM_ROUTINES section of the LSMW object.
form ShowProgress using lvSearch lvTableName.
data: lvMessage type string.
concatenate 'Searching for' lvSearch 'in' lvTableName '. . .'
into lvMessage separated by space.
call function 'SAPGUI_PROGRESS_INDICATOR'
exporting
TEXT = lvMessage
exceptions
others = 1.
endform.
form UpdateTableList using lvObject lvTableName lvRecordCount.
if lvRecordCount gt 0. "Found!
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.
endif.
endform.
form DeepSearch using lvValue.
data: lvOBJEK like INOB-OBJEK, lvCUOBJ like INOB-CUOBJ.
select * from DD03L into lDD03L where FIELDNAME eq 'OBJEK'
order by TABNAME.
perform ShowProgress using p_MATNR lDD03L-TABNAME.
select * from DD02L into lDD02L where TABNAME eq lDD03L-TABNAME
and TABCLASS eq 'TRANSP' and AS4LOCAL ne 'L'.
nRecordCount = 0.
select OBJEK from (lDD03L-TABNAME) into lvOBJEK.
if lvOBJEK cs P_MATNR. add 1 to nRecordCount. endif.
endselect.
perform UpdateTableList using lvValue lDD03L-TABNAME nRecordCount.
endselect.
endselect.
select * from DD03L into lDD03L where FIELDNAME eq 'CUOBJ'
order by TABNAME.
perform ShowProgress using p_MATNR lDD03L-TABNAME.
select * from DD02L into lDD02L where TABNAME eq lDD03L-TABNAME
and TABCLASS eq 'TRANSP' and AS4LOCAL ne 'L'.
nRecordCount = 0.
select CUOBJ from (lDD03L-TABNAME) into lvOBJEK.
if lvOBJEK cs P_MATNR. add 1 to nRecordCount. endif.
endselect.
perform UpdateTableList using lvValue lDD03L-TABNAME nRecordCount.
endselect.
endselect.
endform.
It's that simple! The object is also available for download here. Version 2.0 has the capability to type a field name and a value and then search for that value in any table where the field occurs.
Update: As of April of 2019, the tool that finds data in any table is maintained as part of Jimbo's LSMW Toolbox and that is where to find the most recent version. It includes the User-defined Routines that are called by this tool.
Old versions:
LSMW_WhereUsed2.txt Version 2.0
LSMW_WhereUsed.txt Version 1.0
