Get DDL from DBC tables

General
Enthusiast

Get DDL from DBC tables

Hi everyone,

I'm tryng to build a set of queries with the objective of getting the DDL of tables only from the info inside the dbc tables (columns, tvm, dbase, tableconstraints...).

I started from the table dbc.columns to get all the info about every field of a table.

Below you can find the work in progress query.

I don't know how to get information about the FORMAT option, I know that the field ColumnFormat contains the format option of the field, but not every field has the FORMAT clause explicitly set in the DDL (if not set it takes the default I think).

How can I understand when a field takes the default format (so no FORMAT clause in the DDL) and when a field has the FORMAT option set in the DDL (maybe also the default format)?

If FORMAT is in the DDL I will put the content of the ColumnFormat field of dbc.columns in the output DDL.

QUERY:

SELECT
DatabaseName
,TableName
,ROWN
,ColumnName !! ' ' !! ColumnType !! CharLength !! CharacterSet !! UpperCaseFlag !! Nullable !! DefaultValue !! CompressValueList

FROM (
SELECT
ROW_NUMBER() OVER (PARTITION BY DatabaseName, Tablename ORDER BY ColumnId DESC) AS ROWN
,C.DatabaseName
,C.Tablename
,trim(C.Columnname) as Columnname
,CASE
WHEN ColumnType = 'AT' THEN 'TIME'
WHEN ColumnType = 'BF' THEN 'BYTE'
WHEN ColumnType = 'BO' THEN 'BLOB'
WHEN ColumnType = 'BV' THEN 'VARBYTE'
WHEN ColumnType = 'CF' THEN 'CHAR'
WHEN ColumnType = 'CO' THEN 'CLOB'
WHEN ColumnType = 'CV' THEN 'VARCHAR'
WHEN ColumnType = 'D' THEN 'DECIMAL' !! '(' !! DecimalTotalDigits !! ',' !! DecimalFractionalDigits !! ')'
WHEN ColumnType = 'DA' THEN 'DATE'
WHEN ColumnType = 'DH' THEN 'INTERVAL DAY TO HOUR'
WHEN ColumnType = 'DM' THEN 'INTERVAL DAY TO MINUTE'
WHEN ColumnType = 'DS' THEN 'INTERVAL DAY TO SECOND'
WHEN ColumnType = 'DY' THEN 'INTERVAL DAY'
WHEN ColumnType = 'F' THEN 'FLOAT'
WHEN ColumnType = 'HM' THEN 'INTERVAL HOUR TO MINUTE'
WHEN ColumnType = 'HR' THEN 'INTERVAL HOUR'
WHEN ColumnType = 'HS' THEN 'INTERVAL HOUR TO SECOND'
WHEN ColumnType = 'I1' THEN 'BYTEINT'
WHEN ColumnType = 'I2' THEN 'SMALLINT'
WHEN ColumnType = 'I8' THEN 'BIGINT'
WHEN ColumnType = 'I' THEN 'INTEGER'
WHEN ColumnType = 'MI' THEN 'INTERVAL MINUTE'
WHEN ColumnType = 'MO' THEN 'INTERVAL MONTH'
WHEN ColumnType = 'MS' THEN 'INTERVAL MINUTE TO SECOND'
WHEN ColumnType = 'PD' THEN 'PERIOD(DATE)'
WHEN ColumnType = 'PM' THEN 'PERIOD(TIMESTAMP(n) WITH TIME ZONE)'
WHEN ColumnType = 'PS' THEN 'PERIOD(TIMESTAMP(n))'
WHEN ColumnType = 'PT' THEN 'PERIOD(TIME(n))'
WHEN ColumnType = 'PZ' THEN 'PERIOD(TIME(n) WITH TIME ZONE)'
WHEN ColumnType = 'SC' THEN 'INTERVAL SECOND'
WHEN ColumnType = 'SZ' THEN 'TIMESTAMP WITH TIME ZONE'
WHEN ColumnType = 'TS' THEN 'TIMESTAMP'
WHEN ColumnType = 'TZ' THEN 'TIME WITH TIME ZONE'
WHEN ColumnType = 'YM' THEN 'INTERVAL YEAR TO MONTH'
WHEN ColumnType = 'YR' THEN 'INTERVAL YEAR'
WHEN ColumnType = 'UT' THEN 'UDTType'
END AS ColumnType
,CASE
WHEN Nullable = 'N' THEN ' NOT NULL'
ELSE ''
END AS Nullable
,CASE
WHEN CharType <> 0 THEN SUBSTR(ColumnFormat,2)
ELSE ''
END AS CharLength
,CASE
WHEN CharType = 1 THEN ' CHARACTERSET LATIN'
WHEN CharType = 2 THEN ' CHARACTERSET UNICODE'
--WHEN CharType = 3 THEN 'CHARACTERSET KANJISJIS'
--WHEN CharType = 4 THEN 'CHARACTERSET GRAPHIC'
--WHEN CharType = 5 THEN 'CHARACTERSET KANJI1'
--WHEN CharType = 0 THEN '?????????' -- all other datatypes
ELSE ''
END AS CharacterSet
,CASE
WHEN CharType = 1 AND UpperCaseFlag = 'U' THEN ' UPPERCASE NOT CASESPECIFIC'
WHEN CharType = 1 AND UpperCaseFlag = 'C' THEN ' CASESPECIFIC'
WHEN CharType = 1 AND UpperCaseFlag = 'N' THEN ' NOT CASESPECIFIC'
WHEN CharType = 1 AND UpperCaseFlag = 'B' THEN ' UPPERCASE CASESPECIFIC'
ELSE ''
END AS UpperCaseFlag
,CASE
WHEN DefaultValue IS NOT NULL THEN ' DEFAULT ' !! UPPER(DefaultValue)
ELSE ''
END AS DefaultValue
,CASE
WHEN Compressible = 'C' AND CompressValueList LIKE '%,%' THEN ' COMPRESS ' !! CompressValueList
WHEN Compressible = 'C' AND CompressValueList NOT LIKE '%,%' THEN ' COMPRESS ' !! SUBSTR(CompressValueList,2,position(')' in CompressValueList) - 2)
WHEN Compressible = 'N' THEN ''
END AS CompressValueList
FROM DBC.Columns C

) T

order by 1,2,3 DESC
1 REPLY
Teradata Employee

Re: Get DDL from DBC tables

Hi Giovanni,

If a format clause is not specified for a column in the DDL, then no information is stored in the data dictionary format field for that column and the default format applies.

Use the tdlocaldef utility to display the default formats for each type from the SDF.

- David Craig