I want to get the actual column type from teradata system tables like dbc.columns.
This table have column columntype but it does not give the actual datatype.
I can get output with
select type(columnname) from table
output: varchar2(20)
but there are 1000 tables and 50000 columns. Please suggest some query that can give me actual column type of column from metadata itself
For views the columntype will be NULL, because it's resolved at runtime.
For a single table you can do a HELP COLUMN viewname.*; to get the actual datatypes.
Btw, there's no such type like Varchar2 in Teradata, that's Oracle :-)
Edit: Seems like you want the full definition of a column. This is a SQL UDF returning the same info you find in a SHOW TABLE (I'm not shure if TD14.10 is covered, too, i didn't check, yet):
REPLACE FUNCTION DataTypeString
(
ColumnType CHAR(2),
ColumnLength INT,
DecimalTotalDigits SMALLINT,
DecimalFractionalDigits SMALLINT,
CharType SMALLINT,
ColumnUDTName VARCHAR(128) CHARACTER SET UNICODE
)
RETURNS VARCHAR(60)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
CASE ColumnType
WHEN 'BF' THEN 'BYTE(' || TRIM(CAST(ColumnLength AS INTEGER)) || ')'
WHEN 'BV' THEN 'VARBYTE(' || TRIM(CAST(ColumnLength AS INTEGER)) || ')'
WHEN 'CF' THEN 'CHAR(' || TRIM(CAST(ColumnLength AS INTEGER)) || ')'
WHEN 'CV' THEN 'VARCHAR(' || TRIM(CAST(ColumnLength AS INTEGER)) || ')'
WHEN 'D ' THEN 'DECIMAL(' || TRIM(DecimalTotalDigits) || ','
|| TRIM(DecimalFractionalDigits) || ')'
WHEN 'DA' THEN 'DATE'
WHEN 'F ' THEN 'FLOAT'
WHEN 'I1' THEN 'BYTEINT'
WHEN 'I2' THEN 'SMALLINT'
WHEN 'I8' THEN 'BIGINT'
WHEN 'I ' THEN 'INTEGER'
WHEN 'AT' THEN 'TIME(' || TRIM(DecimalFractionalDigits) || ')'
WHEN 'TS' THEN 'TIMESTAMP(' || TRIM(DecimalFractionalDigits) || ')'
WHEN 'TZ' THEN 'TIME(' || TRIM(DecimalFractionalDigits) || ')' || ' WITH TIME ZONE'
WHEN 'SZ' THEN 'TIMESTAMP(' || TRIM(DecimalFractionalDigits) || ')' || ' WITH TIME ZONE'
WHEN 'YR' THEN 'INTERVAL YEAR(' || TRIM(DecimalTotalDigits) || ')'
WHEN 'YM' THEN 'INTERVAL YEAR(' || TRIM(DecimalTotalDigits) || ')' || ' TO MONTH'
WHEN 'MO' THEN 'INTERVAL MONTH(' || TRIM(DecimalTotalDigits) || ')'
WHEN 'DY' THEN 'INTERVAL DAY(' || TRIM(DecimalTotalDigits) || ')'
WHEN 'DH' THEN 'INTERVAL DAY(' || TRIM(DecimalTotalDigits) || ')' || ' TO HOUR'
WHEN 'DM' THEN 'INTERVAL DAY(' || TRIM(DecimalTotalDigits) || ')' || ' TO MINUTE'
WHEN 'DS' THEN 'INTERVAL DAY(' || TRIM(DecimalTotalDigits) || ')' || ' TO SECOND('
|| TRIM(DecimalFractionalDigits) || ')'
WHEN 'HR' THEN 'INTERVAL HOUR(' || TRIM(DecimalTotalDigits) || ')'
WHEN 'HM' THEN 'INTERVAL HOUR(' || TRIM(DecimalTotalDigits) || ')' || ' TO MINUTE'
WHEN 'HS' THEN 'INTERVAL HOUR(' || TRIM(DecimalTotalDigits) || ')' || ' TO SECOND('
|| TRIM(DecimalFractionalDigits) || ')'
WHEN 'MI' THEN 'INTERVAL MINUTE(' || TRIM(DecimalTotalDigits) || ')'
WHEN 'MS' THEN 'INTERVAL MINUTE(' || TRIM(DecimalTotalDigits) || ')' || ' TO SECOND('
|| TRIM(DecimalFractionalDigits) || ')'
WHEN 'SC' THEN 'INTERVAL SECOND(' || TRIM(DecimalTotalDigits) || ','
|| TRIM(DecimalFractionalDigits) || ')'
WHEN 'BO' THEN 'BLOB(' || TRIM(CAST(ColumnLength AS INTEGER)) || ')'
WHEN 'CO' THEN 'CLOB(' || TRIM(CAST(ColumnLength AS INTEGER)) || ')'
WHEN 'PD' THEN 'PERIOD(DATE)'
WHEN 'PM' THEN 'PERIOD(TIMESTAMP('|| TRIM(DecimalFractionalDigits) || ')' || ' WITH TIME ZONE'
WHEN 'PS' THEN 'PERIOD(TIMESTAMP('|| TRIM(DecimalFractionalDigits) || '))'
WHEN 'PT' THEN 'PERIOD(TIME(' || TRIM(DecimalFractionalDigits) || '))'
WHEN 'PZ' THEN 'PERIOD(TIME(' || TRIM(DecimalFractionalDigits) || '))' || ' WITH TIME ZONE'
WHEN 'UT' THEN COALESCE(ColumnUDTName, '<Unknown> ' || ColumnType)
WHEN '++' THEN 'TD_ANYTYPE'
WHEN 'N' THEN 'NUMBER(' || CASE WHEN DecimalTotalDigits = -128 THEN '*' ELSE TRIM(DecimalTotalDigits) END
|| CASE WHEN DecimalFractionalDigits IN (0, -128) THEN '' ELSE ',' || TRIM(DecimalFractionalDigits) END
|| ')'
WHEN 'A1' THEN COALESCE('SYSUDTLIB.' || ColumnUDTName, '<Unknown> ' || ColumnType)
WHEN 'AN' THEN COALESCE('SYSUDTLIB.' || ColumnUDTName, '<Unknown> ' || ColumnType)
ELSE '<Unknown> ' || ColumnType
END
|| CASE
WHEN ColumnType IN ('CV', 'CF', 'CO')
THEN CASE CharType
WHEN 1 THEN ' CHARACTER SET LATIN'
WHEN 2 THEN ' CHARACTER SET UNICODE'
WHEN 3 THEN ' CHARACTER SET KANJISJIS'
WHEN 4 THEN ' CHARACTER SET GRAPHIC'
WHEN 5 THEN ' CHARACTER SET KANJI1'
ELSE ''
END
ELSE ''
END
;
I found this statement
SELECT
DATABASENAME, TABLENAME, COLUMNNAME, TRIM(COLUMNTYPE)||'('||TRIM(COLUMNNUM)||')'
FROM (
SELECT DATABASENAME, TABLENAME, COLUMNNAME,
CASE
WHEN COLUMNTYPE='CF' THEN 'CHAR'
WHEN COLUMNTYPE='CV' THEN 'VARCHAR'
WHEN COLUMNTYPE='D' THEN 'DECIMAL'
WHEN COLUMNTYPE='TS' THEN 'TIMESTAMP'
WHEN COLUMNTYPE='I' THEN 'INTEGER'
WHEN COLUMNTYPE='I2' THEN 'SMALLINT'
WHEN COLUMNTYPE='DA' THEN 'DATE'
END AS COLUMNTYPE,
CASE
WHEN COLUMNTYPE='CF' THEN COLUMNLENGTH
WHEN COLUMNTYPE='CV' THEN COLUMNLENGTH
WHEN COLUMNTYPE='D' THEN (DECIMALTOTALDIGITS||','||DECIMALFRACTIONALDIGITS)
WHEN COLUMNTYPE='TS' THEN COLUMNLENGTH
WHEN COLUMNTYPE='I' THEN DECIMALTOTALDIGITS
WHEN COLUMNTYPE='I2' THEN DECIMALTOTALDIGITS
WHEN COLUMNTYPE='DA' THEN NULL
END AS COLUMNNUM
FROM DBC.COLUMNS
WHERE DATABASENAME='your database'
) TBL
Going by Retrieving column and other metadata information in Teradata I'd suggest to extract the names of tables and columns from the DBC tables and then build a 'simple script' to fetch all the information. I have no clue about TD but going from the Quick Reference it shouldn't be too difficult to create a loop that fetches the results dynamically and inserts it in your own table.
Finally a good case for proposing a cursor =)
