使用teradata系统表获取列类型

I want to get the actual column type from teradata system tables like dbc.columns.

我希望从teradata系统表(如dbc.columns)中获得实际的列类型。

This table have column columntype but it does not give the actual datatype.

该表具有列columntype，但不提供实际的数据类型。

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

但是有1000个表和50000个列。请建议一些查询，可以从元数据本身给我实际的列类型

3 个解决方案

#1

For views the columntype will be NULL, because it's resolved at runtime.

对于视图，columntype将为NULL，因为它在运行时解析。

For a single table you can do a HELP COLUMN viewname.*; to get the actual datatypes.

对于单个表，可以执行帮助列viewname.*;获取实际数据类型。

Btw, there's no such type like Varchar2 in Teradata, that's Oracle :-)

顺便说一句，在Teradata中没有像Varchar2这样的类型，那是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):

编辑:看起来你想要一个列的完整定义。这是一个SQL UDF，返回您在SHOW TABLE中找到的相同信息(如果TD14.10也包括在内，我不确定，我还没有检查):

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
;

#2

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

#3

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.

通过在Teradata中检索列和其他元数据信息，我建议从DBC表中提取表和列的名称，然后构建一个“简单脚本”来获取所有信息。我对TD一无所知，但是从快速引用的角度来看，创建一个动态获取结果并将其插入到您自己的表中的循环应该不会太难。

Finally a good case for proposing a cursor =)

最后，建议使用游标=)

#1