All Forums General
brokenp87 7 posts Joined 05/12
08 Apr 2014
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

 

david.craig 73 posts Joined 05/13
08 Apr 2014

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
 

You must sign in to leave a comment.