Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
stephenl1
Participant

Introduction


In our organisation we were upgrading SAP and neded a quick way to associate various objects with their object type and description, however these were not obviously available via SQL, so a solution was required. The primary table for objects was TADIR, but this only contained a code for obect type, so the description had to be determined from elsewhere. Note: this was required for some quick analysis; for ABAP developers there are standard methods for obtaining descriptions.

In trying to solve this problem I discovered two interesting facts.

  1. Object type descriptions are not fully held in tables, some are hardcoded as text symbols.

  2. Object descriptions are not stored centrally in a single table, but rather scattered throughout many tables.


Defining the solution


Obtaining Object Type Descriptions


To obtain a full list of object type descriptions I first went into SE11 and looked up table TADIR, then searched through to see if field OBJECT had a value range or value table defined against its domain. It did not.  Next I looked to see if it had a search help defined against it. I thought if I pressed F4 against this field in SE16 that I could copy out the produced list of values, however the display restricted the list to an incomplete list of the first 500 values only.  So I needed to find out where the search help was retrieving the values from.  I drilled into the Search Help code and added some breakpoints for debugging, then went back into SE16 and pressed F4 again.  Through debug I was able to find the function module SAPLTR_OBJECTS and the two forms involved

GET_LOGICAL_TYPES

GET_SYSTEM_TYPES

I identified Logical types as coming from tables OBJH and OBJT, so I could code these tables direct in SQL

System types were coming from text symbols, so could not be coded into SQL using tables.

I was able to export the table PT_SYSTYPE[] from within the debugger after it had been loaded in GET_SYSTEM_TYPES.  I placed this in EXCEL where I could create a calculation to change the values into SQL Insert into statements, so I could then copy these statements into SQL.

I created a temporary table in SQL and loaded both sets of values into it, ready for use when joining to table TADIR.

Obtaining Object Descriptions


Object descriptions are not centrally located in a single table, as I would have liked, but are found in may tables across the database.  The code below does not cover all object types and may not be 100% reliable for object descriptions.  It is a best attempt based on my searching through SAP and the Internet for the correct tables to obtain the descriptions.  I welcome any corrections and additions in your comments.

The solution I came up with


In the code below you need to change the word "catalog" to your catalog for temporary tables.
/*
First create a table to hold the object type descriptions for field OBJECT from table TADIR
*/

create local temporary table catalog.#object_types(pgmid VARCHAR(4), object_type varchar(4), description VARCHAR(255));

/*
Note: Object type descriptions are not fully held in tables, some are hardcoded.
To obtain the complete list one must open table TADIR in SE16 and do an F4 (Search
Help) on field Object, while in debugger.

There are two lists required to be combined.
- Logical Types
- System Types

Logical Types are extracted in function module SAPLTR_OBJECTS in form GET_LOGICAL_TYPES
They are stored in table st_logical_types. These types are taken from database tables
OBJH and OBJT.

System types are hard coded in function module SAPLTR_OBJECTS in form GET_SYSTEM_TYPES
These can be extracted by running the Search Help (F4) on column OBJECT of table TADIR
then placing a breakpoint at the end of form GET_SYSTEM_TYPES, then viewing and exporting
inernal table PT_SYSTYPE[]. Once in a spreadsheet a EXCEL calculation can be writen to
format insert statements as below. The following EXCEL calculation converts row 2 of the
exported table, and can be copied down for all rows.
="Insert into catalog.#object_types values('" & B2 & "', '" & C2 & "', '" & D2 & "');"
*/

/*
=================
Get Logical Types
=================
*/

Insert into catalog.#object_types

Select 'R3TR' as pgmid
, objt.objectname as object_type
, coalesce(objt.ddtext,'Logical Transport Object') as description

from objh as objh

left outer join objt as objt
on objt.objectname = objh.objectname
and objt.objecttype = 'L'
and objt.language = 'E'

where objh.objecttype = 'L' ;

/*
=================
Load System Types
=================
*/

Insert into catalog.#object_types values('*', '', 'Comment Line');
Insert into catalog.#object_types values('CORR', 'MERG', 'Comment: Object list was added');
Insert into catalog.#object_types values('CORR', 'PERF', 'Perforce Changelist');
Insert into catalog.#object_types values('CORR', 'RELE', 'Comment Entry: Released');
Insert into catalog.#object_types values('LIMU', 'ADIR', 'Object Directory Entry');
Insert into catalog.#object_types values('LIMU', 'CINC', 'Class Include (ABAP Objects)');
Insert into catalog.#object_types values('LIMU', 'CLSD', 'Class Definition (ABAP Objects)');
Insert into catalog.#object_types values('LIMU', 'COMM', 'Object List of Request or Piece List');
Insert into catalog.#object_types values('LIMU', 'CPRI', 'Private Header (ABAP Objects)');
Insert into catalog.#object_types values('LIMU', 'CPRO', 'Protected Header (ABAP Objects)');
Insert into catalog.#object_types values('LIMU', 'CPUB', 'Public Header (ABAP Objects)');
Insert into catalog.#object_types values('LIMU', 'CUAD', 'GUI Definition');
Insert into catalog.#object_types values('LIMU', 'DEVP', 'Package: Usage');
Insert into catalog.#object_types values('LIMU', 'DOCU', 'Documentation');
Insert into catalog.#object_types values('LIMU', 'DOMD', 'Domain Definition');
Insert into catalog.#object_types values('LIMU', 'DTED', 'Data Element Definition');
Insert into catalog.#object_types values('LIMU', 'DYNP', 'Screen');
Insert into catalog.#object_types values('LIMU', 'ENQD', 'Lock Object Definition');
Insert into catalog.#object_types values('LIMU', 'FSEL', 'Field Selection');
Insert into catalog.#object_types values('LIMU', 'FUNC', 'Function Module');
Insert into catalog.#object_types values('LIMU', 'FUGT', 'Function Group Texts');
Insert into catalog.#object_types values('LIMU', 'HOTO', 'Single Object (SAP HANA Transport for ABAP)');
Insert into catalog.#object_types values('LIMU', 'HOTP', 'Package Metadata (SAP HANA Transport for ABAP)');
Insert into catalog.#object_types values('LIMU', 'INDX', 'Table Index');
Insert into catalog.#object_types values('LIMU', 'INTD', 'Interface Definition (ABAP Objects)');
Insert into catalog.#object_types values('LIMU', 'MAPP', 'Mapping Information (ABAP Objects)');
Insert into catalog.#object_types values('LIMU', 'MCOD', 'Matchcode Object Definition');
Insert into catalog.#object_types values('LIMU', 'MESS', 'Single Message');
Insert into catalog.#object_types values('LIMU', 'METH', 'Method (ABAP Objects)');
Insert into catalog.#object_types values('LIMU', 'MSAD', 'Message Class: Definition and All Short Texts');
Insert into catalog.#object_types values('LIMU', 'PIFA', 'Package Interface: Assignments');
Insert into catalog.#object_types values('LIMU', 'PIFH', 'Package Interface: Header Information');
Insert into catalog.#object_types values('LIMU', 'REPO', 'Report Program Source Code and Texts');
Insert into catalog.#object_types values('LIMU', 'REPS', 'Report Source Code');
Insert into catalog.#object_types values('LIMU', 'REPT', 'Report Texts');
Insert into catalog.#object_types values('LIMU', 'SHLD', 'Search Help Definition');
Insert into catalog.#object_types values('LIMU', 'SHLX', 'Text Component of Search Help Definition');
Insert into catalog.#object_types values('LIMU', 'SOTT', 'Concept (Online Text Repository) - Short Texts');
Insert into catalog.#object_types values('LIMU', 'SOTU', 'Concept (Online Text Repository) - Long Texts');
Insert into catalog.#object_types values('LIMU', 'SQLD', 'Pool, Cluster Definition');
Insert into catalog.#object_types values('LIMU', 'SQTT', 'Technical Attributes for Pool/Cluster');
Insert into catalog.#object_types values('LIMU', 'TABD', 'Table Definition');
Insert into catalog.#object_types values('LIMU', 'TABT', 'Technical Attributes of a Table');
Insert into catalog.#object_types values('LIMU', 'TTYD', 'Table Type Definition');
Insert into catalog.#object_types values('LIMU', 'TTYX', 'Text Component of Table Type Definition');
Insert into catalog.#object_types values('LIMU', 'TYPD', 'Type Group Source Code');
Insert into catalog.#object_types values('LIMU', 'VARI', 'Report Program System Variant');
Insert into catalog.#object_types values('LIMU', 'VARX', 'Report Program Application Variant');
Insert into catalog.#object_types values('LIMU', 'VIED', 'View Definition');
Insert into catalog.#object_types values('LIMU', 'VIET', 'Technical Attributes of a View');
Insert into catalog.#object_types values('LIMU', 'WAPD', 'Definition of BSP Application');
Insert into catalog.#object_types values('LIMU', 'WAPP', 'Page/Controller of a BSP Application');
Insert into catalog.#object_types values('LIMU', 'WDYC', 'Controller (Web Dynpro)');
Insert into catalog.#object_types values('LIMU', 'WDYD', 'Definition (Web Dynpro)');
Insert into catalog.#object_types values('LIMU', 'WDYV', 'View (Web Dynpro)');
Insert into catalog.#object_types values('LIMU', 'XIND', 'Extension Index Definition');
Insert into catalog.#object_types values('R3TR', 'APPL', 'Application Class');
Insert into catalog.#object_types values('R3TR', 'CDAT', 'View Cluster Maintenance: Data');
Insert into catalog.#object_types values('R3TR', 'CLAS', 'Class (ABAP Objects)');
Insert into catalog.#object_types values('R3TR', 'CNTX', 'Context');
Insert into catalog.#object_types values('R3TR', 'DDDD', 'Changes to Nametab Structure');
Insert into catalog.#object_types values('R3TR', 'DDLS', 'Data Definition Language Source');
Insert into catalog.#object_types values('R3TR', 'DEVC', 'Package');
Insert into catalog.#object_types values('R3TR', 'DIAL', 'Dialog Module');
Insert into catalog.#object_types values('R3TR', 'DOCT', 'General Text');
Insert into catalog.#object_types values('R3TR', 'DOCV', 'Documentation (Independent)');
Insert into catalog.#object_types values('R3TR', 'DOMA', 'Domain');
Insert into catalog.#object_types values('R3TR', 'DSEL', 'Selection View');
Insert into catalog.#object_types values('R3TR', 'DRPM', 'Dictionary Replication Metadata');
Insert into catalog.#object_types values('R3TR', 'DSYS', 'Chapter of a Book Structure');
Insert into catalog.#object_types values('R3TR', 'DTEL', 'Data Element');
Insert into catalog.#object_types values('R3TR', 'ENQU', 'Lock Object');
Insert into catalog.#object_types values('R3TR', 'FORM', 'SAPscript Form');
Insert into catalog.#object_types values('R3TR', 'FUGR', 'Function Group');
Insert into catalog.#object_types values('R3TR', 'FUGS', 'Function Group with Customer Include: SAP Part');
Insert into catalog.#object_types values('R3TR', 'FUGX', 'Function Group with Customer Include: Customer Part');
Insert into catalog.#object_types values('R3TR', 'HOTA', 'Full Package (SAP HANA Transport for ABAP)');
Insert into catalog.#object_types values('R3TR', 'INTF', 'Interface (ABAP Objects)');
Insert into catalog.#object_types values('R3TR', 'LDBA', 'Logical Database');
Insert into catalog.#object_types values('R3TR', 'MCID', 'Matchcode ID');
Insert into catalog.#object_types values('R3TR', 'MCOB', 'Matchcode Object');
Insert into catalog.#object_types values('R3TR', 'MSAG', 'Message Class');
Insert into catalog.#object_types values('R3TR', 'PARA', 'SPA/GPA Parameters');
Insert into catalog.#object_types values('R3TR', 'PINF', 'Package interface');
Insert into catalog.#object_types values('R3TR', 'PROG', 'Program');
Insert into catalog.#object_types values('R3TR', 'SHLP', 'Search Help');
Insert into catalog.#object_types values('R3TR', 'SOTR', 'All Concepts (OTR) of a Package - Short Texts');
Insert into catalog.#object_types values('R3TR', 'SOTS', 'All Concepts (OTR) of a Package - Long Texts');
Insert into catalog.#object_types values('R3TR', 'SQLT', 'Pooled/Cluster Table');
Insert into catalog.#object_types values('R3TR', 'SQSC', 'Database Procedure Proxy');
Insert into catalog.#object_types values('R3TR', 'STOB', 'Structured Object');
Insert into catalog.#object_types values('R3TR', 'STYL', 'SAPscript Style');
Insert into catalog.#object_types values('R3TR', 'SYAG', 'System Log Messages');
Insert into catalog.#object_types values('R3TR', 'SYND', 'Syntax Documentation');
Insert into catalog.#object_types values('R3TR', 'TABL', 'Table');
Insert into catalog.#object_types values('R3TR', 'TABU', 'Table Contents');
Insert into catalog.#object_types values('R3TR', 'TDAT', 'Customizing: Table Contents');
Insert into catalog.#object_types values('R3TR', 'TEXT', 'SAPscript Text');
Insert into catalog.#object_types values('R3TR', 'TOBJ', 'Definition of a Maintenance and Transport Object');
Insert into catalog.#object_types values('R3TR', 'TRAN', 'Transaction');
Insert into catalog.#object_types values('R3TR', 'TTYP', 'Table Type');
Insert into catalog.#object_types values('R3TR', 'TYPE', 'Type Group');
Insert into catalog.#object_types values('R3TR', 'VDAT', 'View Maintenance: Data');
Insert into catalog.#object_types values('R3TR', 'VERS', 'Version Number');
Insert into catalog.#object_types values('R3TR', 'VIEW', 'View');
Insert into catalog.#object_types values('R3TR', 'WAPA', 'BSP (Business Server Pages) Application');
Insert into catalog.#object_types values('R3TR', 'WDYN', 'Web Dynpro Component');
Insert into catalog.#object_types values('R3TR', 'XINX', 'Ext. Index');
Insert into catalog.#object_types values('R3TR', 'XPRA', 'Program Run after Transport');

/*
================================
Select TADIR entries for Package
================================
*/

Select tadi.devclass
, tadi.object
, objt.description
, tadi.obj_name
, case tadi.object
when 'TABL' then tabl.ddtext -- Table
when 'TTYP' then ttyp.ddtext -- Table type
when 'VIEW' then view.ddtext -- view
when 'ENQU' then view.ddtext -- lock object
when 'PROG' then prog.text -- program
when 'REPO' then prog.text -- report
when 'FUGR' then fugr.areat -- function group
when 'DOMA' then doma.ddtext -- domain
when 'DTEL' then dtel.ddtext -- data elements
when 'SHLP' then shlp.ddtext -- search help
when 'MSGC' then shlp.ddtext -- Message class (not sure of this one)
when 'DEVC' then devc.ctext -- Package
when 'AUTH' then auth.ttext -- Authorisation object??
when 'TRAN' then tran.ttext -- transaction code
when 'SUSO' then auth.ttext -- Authorisation object
when 'SUST' then auth.ttext --
when 'SSFO' then ssfo.caption -- smartforms
when 'SSST' then ssst.caption -- smartstyles
when 'FORM' then stxh.tdtitle -- SAP Script
when 'ACID' then acid.descript -- Checkpoint group
when 'CLAS' then clas.descript -- Class
when 'INTF' then clas.descript -- Class
when 'VCLS' then vcls.objecttext -- view cluster
when 'CUS0' then cus0.text -- Customizing Object ??
when 'CUS1' then cus0.text -- Customizing Object ??
when 'CUS2' then cus0.text -- Customizing Object ??
when 'SOBJ' then sobj.ntext -- Texts Basic Data
when 'MSAG' then msag.stext -- Table T100A text
when 'UDMO' then udmo.langbez -- DM Data Model Short Text
when 'UENO' then ueno.langbez -- DM Entity Type Short Text
when 'PDTS' then pdts.stext -- Standard Infotype 1000 (SAP) Object Existence
when 'PDWS' then pdts.stext -- Standard Infotype 1000 (SAP) Object Existence
when 'PDAC' then pdac.short -- View: Text Table for Rules
when 'PARA' then para.partext -- Memory ID Short Texts
when 'SCAT' then scat.ktext -- CATT: Basic Texts for Test Procedure
when 'CMOD' then cmod.modtext -- Enhancement Projects - Short Texts
when 'LDBA' then ldba.ldbtext -- Texts for logical databases
when 'AQBG' then aqbg.text -- SAP Query: Texts for User Groups ??
when 'AQQU' then aqqu.text -- SP Query: Texts for Queries ??
when 'AQSG' then aqsg.text -- SAP Query: Texts for Functional Areas ??
when 'MCOB' then mcob.mctext -- AS400-T_MCOBJECT: MC Object Texts
end as Description

from tadir as tadi

left outer join catalog.#object_types as objt
on objt.object_type = tadi.object

left outer join trdirt as prog -- program descriptions
on prog.sprsl = 'E'
and prog.name = tadi.obj_name

left outer join dd02t as tabl -- table descriptions
on tabl.ddlanguage = 'E'
and tabl.tabname = tadi.obj_name

left outer join tlibt as fugr -- function group descriptions
on fugr.spras = 'E'
and fugr.area = tadi.obj_name

left outer join dd01t as doma -- domain descriptions
on doma.ddlanguage = 'E'
and doma.domname = tadi.obj_name

left outer join dd04t as dtel -- data elements
on dtel.ddlanguage = 'E'
and dtel.rollname = tadi.obj_name

left outer join dd25t as view -- view descriptions
on view.ddlanguage = 'E'
and view.viewname = tadi.obj_name

left outer join dd30t as shlp -- search help descriptions
on shlp.ddlanguage = 'E'
and shlp.shlpname = tadi.obj_name

left outer join dd40t as ttyp -- table type descriptions
on ttyp.ddlanguage = 'E'
and ttyp.typename = tadi.obj_name

left outer join t100a as msgc -- message class descriptions
on msgc.masterlang = 'E'
and msgc.arbgb = tadi.obj_name

left outer join tdevct as devc -- package descriptions
on devc.spras = 'E'
and devc.devclass = tadi.obj_name

left outer join tobjt as auth -- authorization object descriptions
on auth.langu = 'E'
and auth.object = tadi.obj_name

left outer join tstct as tran -- transaction code descriptions
on tran.sprsl = 'E'
and tran.tcode = tadi.obj_name

left outer join stxfadmt as ssfo -- smartforms descriptions
on ssfo.langu = 'E'
and ssfo.formname = tadi.obj_name

left outer join stxsadmt as ssst -- smartstyles descriptions
on ssst.langu = 'E'
and ssst.stylename = tadi.obj_name

left outer join stxh as stxh -- SAP Script descriptions
on stxh.tdspras = 'E'
and stxh.tdobject = tadi.object
and stxh.tdname = tadi.obj_name

left outer join seoclasstx as clas -- Class descriptions
on clas.langu = 'E'
and clas.clsname = tadi.obj_name

left outer join AAB_ID_PROPT as acid -- Checkpoint group
on acid.langu = 'E'
and acid.name = tadi.obj_name

left outer join vclstruct as vcls -- View cluster
on vcls.spras = 'E'
and vcls.vclname = tadi.obj_name

left outer join cus_actobt as cus0 -- Customizing
on cus0.spras = 'E'
and (cus0.act_id = tadi.obj_name
or cus0.objectname = tadi.obj_name)

left outer join t100t as msag -- Table T100A text
on msag.sprsl = 'E'
and msag.arbgb = tadi.obj_name

left outer join tojtt as sobj -- Texts Basic Data
on sobj.language = 'E'
and sobj.name = tadi.obj_name

left outer join dm40t as udmo -- DM Data Model Short Text
on udmo.sprache = 'E'
and udmo.dmoid = tadi.obj_name

left outer join dm02t as ueno -- DM Entity Type Short Text
on ueno.sprache = 'E'
and ueno.entid = tadi.obj_name

left outer join hrs1000 as pdts -- Standard Infotype 1000 (SAP) Object Existence
on pdts.langu = 'E'
and pdts.objid = tadi.obj_name

left outer join v_actext as pdac -- View: Text Table for Rules
on pdac.langu = 'E'
and pdac.objid = tadi.obj_name

left outer join tparat as para -- Memory ID Short Texts
on para.sprache = 'E'
and para.paramid = tadi.obj_name

left outer join catg as scat -- CATT: Basic Texts for Test Procedure
on scat.spras = 'E'
and scat.ablnr = tadi.obj_name

left outer join modtext as cmod -- Enhancement Projects - Short Texts
on cmod.sprsl = 'E'
and cmod.name = tadi.obj_name

left outer join ldbt as ldba -- Texts for logical databases
on ldba.spras = 'E'
and ldba.ldbname = tadi.obj_name

left outer join aqgtb as aqbg -- SAP Query: Texts for User Groups
on aqbg.sprsl = 'E'
and aqbg.num = tadi.obj_name

left outer join aqgtq as aqqu -- SP Query: Texts for Queries
on aqqu.sprsl = 'E'
and aqqu.qnum = tadi.obj_name

left outer join aqgts as aqsg -- SAP Query: Texts for Functional Areas
on aqsg.sprsl = 'E'
and aqsg.clas = tadi.obj_name

left outer join dd20t as mcob -- AS400-T_MCOBJECT: MC Object Texts
on mcob.ddlanguage = 'E'
and mcob.mconame = tadi.obj_name

where tadi.devclass = 'FKKBI'

order by object;

/*
========================
Drop table on completion
========================
*/

drop table catalog.#object_types;

Summary


This solution is useful to anyone wanting a quick way to obtain object type description when analysing records outside of ABAP.  It could be more elegant if wrapped into a view, however due to the need to build a temp table of system types, this was not possible. I hope the code proves useful to those using SQL on HANA, and welcome any commentary or suggestions as to where the code may be improved.
5 Comments
Labels in this area