Thursday, March 31, 2011

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
SELECT cast(DB_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 = 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
and JDE900.DD900.F9203.FRDSCA != '*SAME'
--SQL Script end

The results will look something like this:

Object NameTable DescriptionColumn NameData Dictionary DescriptionsColumn LengthData TypeData Type Description
JDE_DEVELOPMENT.TESTDTA.F00023Distributed Next Number Table DNDCTDocument Type22String
JDE_DEVELOPMENT.TESTDTA.F00023Distributed Next Number Table DNCTRYCentury29Numeric
JDE_DEVELOPMENT.TESTDTA.F00023Distributed Next Number Table DNCTRYCentury29Numeric
JDE_DEVELOPMENT.TESTDTA.F00023Distributed Next Number Table DNCNR1Remaining in Range 1159Numeric

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.
Subscribe to Jeff Stevenson's Technology Blog - Get an email when new posts appear


Anonymous said...

Doing a conversion and I need the information on field names without having JDE locally to resource. Is there a way to get field names and their desc or can u provide ?

Jeff Stevenson said...

I would if you had left some contact information.

Brodie said...


Thanks for the code. Is there any reason by JDE_DEV900 & JDE_DEV (data) databases would use different collations?

The implementation is SQL Server.



Jeff Stevenson said...

Take a look at this:

CyberT said...