Skip to Content
Technical Articles

TADIR object types and object descriptions via SQL

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.

Be the first to leave a comment
You must be Logged on to comment or reply to a post.