Three ways to get SQL table field structure

It should be easy to print the structure of your SQL tables – right? Not so fast. Try to search that topic and you will find a handful of relevant, but confusing links.

Here are 3 ways to get the table field structure that I found the most useful:

-01-  Use Alt+F1

Select schema and table name and press Alt+F1 in SSMS, for example highlight CRPDTA.F0101 and use Alt+F1.

-02- Get SQL table structure

Use this T-SQL Query to get table structure from SQL system tables:

One little twist here is that different field types store its size in different columns. This SQL command combines an important CHARACTER_OCTET_LENGTH and NUMERIC_PRECISION and NUMERIC_SCALE fields all in one column.

SELECT ORDINAL_POSITION   [Seq]
, COLUMN_NAME     [Field Name]
, DATA_TYPE     [Type]
, LTRIM(COALESCE(STR(CHARACTER_MAXIMUM_LENGTH)
  , STR(NUMERIC_PRECISION)+','+LTRIM(STR(
  NUMERIC_SCALE)) , ' ')) [Size]
, IS_NULLABLE     [Nullable]
, ISNULL(COLUMN_DEFAULT, ' ') [Def Val]
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'F4211'
ORDER BY ORDINAL_POSITION

Here is what the final result might look like:

-03- Get JDE EnterpriseOne table structure

If your table is from JDE EnterpriseOne, you might need to list field descriptions and actual decimal places for your numeric fields that are stored in Data Dictionary. In that case use SQL below:

SELECT ORDINAL_POSITION [Seq], COLUMN_NAME [Field Name]
, DATA_TYPE [SQL Type]
, LTRIM(COALESCE(STR(CHARACTER_MAXIMUM_LENGTH), STR(NUMERIC_PRECISION)+','+LTRIM(STR(NUMERIC_SCALE)), ' ')) [Size]
, IS_NULLABLE [Nullable], FRDSCR [Description], FRCLAS [Class], FRDTAT [Ty], FRDTAS [Size], FRDTAD [File Dec]
, FRCDEC [Display Dec], FRDVAL [Default Value], FROWER [Edit Rule], FROER1 [UDC SY], FROER2 [UDC RT]
FROM INFORMATION_SCHEMA.COLUMNS LEFT OUTER JOIN
JDE920.DD920.F9202 T02 ON SUBSTRING(COLUMN_NAME, 3, 10) = T02.FRDTAI AND FRLNGP = '' AND FRSYR = ''
LEFT OUTER JOIN JDE920.DD920.F9210 T10 ON SUBSTRING(COLUMN_NAME, 3, 10) = T10.FRDTAI
WHERE TABLE_NAME = 'F0911' ORDER BY ORDINAL_POSITION

You might see output similar to table below:

Note: for SQL above to work you need SQL read access to JDE system database and DD files F9202 and F9210. To check where your DD is located use data browser and navigate Data Source Master table F98611.

[Note: last updated 2019-03-14 TH 13:05]

Beware of false prophets:

“Few people think more than two or three times a year. I’ve made an international reputation for myself by thinking once or twice a week.”
Bernard Shaw