EnterpriseOne Table List With Column Information

I recently saw a request for a complete list of EnterpriseOne tables with column specifications and other information. Getting these details using a SQL script is not simple since JD Edwards seems to violate several standards in SQL-92 and database normalization best practices.

Since the required information is not kept in one table it is necessary to use SQL joins and foreign key relationships to gather all information into one result set. Here's the script:


--SQL Script begin
USE JDE_DEVELOPMENT
SELECT cast(DB_NAME() + '.' + sys.schemas.name + '.' + sys.objects.name as char(40)) as 'Object Name'
, JDE900.OL900.F9860.SIMD as 'Table Description'
, JDE_DV900.DV900.F98711.TDSQLC as 'Column Name'
, JDE900.DD900.F9203.FRDSCA as 'Data Dictionary Descriptions'
, JDE900.DD900.F9210.FRDTAS as 'Column Length'
, JDE900.DD900.F9210.FROWTP as 'Data Type'
, JDE_DEVELOPMENT.TESTCTL.F0005.DRDL01 as 'Data Type Description'
FROM sys.objects
JOIN sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
JOIN JDE900.OL900.F9860 on sys.objects.name = JDE900.OL900.F9860.SIOBNM
JOIN JDE_DV900.DV900.F98711 on JDE900.OL900.F9860.SIOBNM = JDE_DV900.DV900.F98711.TDOBNM
JOIN JDE900.DD900.F9203 on JDE_DV900.DV900.F98711.TDOBND = JDE900.DD900.F9203.FRDTAI
JOIN JDE900.DD900.F9210 on JDE_DV900.DV900.F98711.TDOBND = JDE900.DD900.F9210.FRDTAI
JOIN JDE_DEVELOPMENT.TESTCTL.F0005 on JDE900.DD900.F9210.FROWTP = LTRIM(JDE_DEVELOPMENT.TESTCTL.F0005.DRKY)
WHERE JDE_DEVELOPMENT.TESTCTL.F0005.DRSY = 'H98' and JDE_DEVELOPMENT.TESTCTL.F0005.DRRT = 'DT'
and JDE900.DD900.F9203.FRDSCA != '*SAME'
ORDER BY JDE900.OL900.F9860.SIOBNM
--SQL Script end


The results will look something like this:

Object Name	Table Description	Column Name	Data Dictionary Descriptions	Column Length	Data Type	Data Type Description
JDE_DEVELOPMENT.TESTDTA.F00023	Distributed Next Number Table	DNDCT	Document Type	2	2	String
JDE_DEVELOPMENT.TESTDTA.F00023	Distributed Next Number Table	DNCTRY	Century	2	9	Numeric
JDE_DEVELOPMENT.TESTDTA.F00023	Distributed Next Number Table	DNCTRY	Century	2	9	Numeric
JDE_DEVELOPMENT.TESTDTA.F00023	Distributed Next Number Table	DNCNR1	Remaining in Range 1	15	9	Numeric

There will be a row in the results set for each Data Dictionary description of each column in every table in the chosen database.  You will be able to see the complete object name, including schema, the Object Librarian description, the name of the column according to Central Objects specs, the possible descriptions of the column according to Data Dictionary, the column length according to Data Dictionary specifications, the data type according to Data Dictionary specifications, and the data type description according to Data Dictionary specifications.

You may notice that you have duplicate entries in the Column Name and Data Dictionary Descriptions fields.  The EnterpriseOne Data Dictionary often has multiple descriptions for the same data item, determining which description to use in which table or form using code.  In general though, you can be pretty sure that the data returned will give you an idea of what the column is used for when you combine the Data Dictionary description with the table description.

If you wish to analyze a different database you must change the highlighted items. 

If you have any questions about how or why I did something in the script leave a comment and I'll do my best to answer.