Datatype of a column from dbc tables

General

Datatype of a column from dbc tables

Hello all

Is it possible to get the datatype of a column from any of the dbc tables, instead of deriving the same based on Columntype?

Thank you

8 REPLIES
Enthusiast

Re: Datatype of a column from dbc tables

Use dbc. columnsv

Rglass

Teradata Employee

Re: Datatype of a column from dbc tables

Are you asking about getting the keyword spelled out, e.g. SMALLINT vs. I2?  Then the answer is no, that's not in any DBC table.

Depending on what you are trying to do, you might consider using the TYPE attribute function, e.g.

SELECT TOP 1 TYPE(col1) FROM mydb.mytable;

Junior Contributor

Re: Datatype of a column from dbc tables

This is a UDF I wrote some tme ago:

/**********
Returns the datatype of a column as a literal

20111012 initial version - Dieter Noeth
20131010 added TD14.10 ARRAY datatypes - dn
20140812 added TD15 JSON / TD_VALIST datatype - dn
**********/
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(ColumnLength (FORMAT '-(9)9')) || ')'
WHEN 'BV' THEN 'VARBYTE(' || TRIM(ColumnLength (FORMAT 'Z(9)9')) || ')'
WHEN 'CF' THEN 'CHAR(' || TRIM(ColumnLength (FORMAT 'Z(9)9')) || ')'
WHEN 'CV' THEN 'VARCHAR(' || TRIM(ColumnLength (FORMAT 'Z(9)9')) || ')'
WHEN 'D ' THEN 'DECIMAL(' || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ','
|| TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')'
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 (FORMAT '-(9)9')) || ')'
WHEN 'TS' THEN 'TIMESTAMP(' || TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')'
WHEN 'TZ' THEN 'TIME(' || TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')' || ' WITH TIME ZONE'
WHEN 'SZ' THEN 'TIMESTAMP(' || TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')' || ' WITH TIME ZONE'
WHEN 'YR' THEN 'INTERVAL YEAR(' || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')'
WHEN 'YM' THEN 'INTERVAL YEAR(' || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')' || ' TO MONTH'
WHEN 'MO' THEN 'INTERVAL MONTH(' || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')'
WHEN 'DY' THEN 'INTERVAL DAY(' || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')'
WHEN 'DH' THEN 'INTERVAL DAY(' || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')' || ' TO HOUR'
WHEN 'DM' THEN 'INTERVAL DAY(' || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')' || ' TO MINUTE'
WHEN 'DS' THEN 'INTERVAL DAY(' || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')' || ' TO SECOND('
|| TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')'
WHEN 'HR' THEN 'INTERVAL HOUR(' || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')'
WHEN 'HM' THEN 'INTERVAL HOUR(' || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')' || ' TO MINUTE'
WHEN 'HS' THEN 'INTERVAL HOUR(' || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')' || ' TO SECOND('
|| TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')'
WHEN 'MI' THEN 'INTERVAL MINUTE(' || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')'
WHEN 'MS' THEN 'INTERVAL MINUTE(' || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')' || ' TO SECOND('
|| TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')'
WHEN 'SC' THEN 'INTERVAL SECOND(' || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ','
|| TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')'
WHEN 'BO' THEN 'BLOB(' || TRIM(ColumnLength (FORMAT 'Z(9)9')) || ')'
WHEN 'CO' THEN 'CLOB(' || TRIM(ColumnLength (FORMAT 'Z(9)9')) || ')'

WHEN 'PD' THEN 'PERIOD(DATE)'
WHEN 'PM' THEN 'PERIOD(TIMESTAMP('|| TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')' || ' WITH TIME ZONE)'
WHEN 'PS' THEN 'PERIOD(TIMESTAMP('|| TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || '))'
WHEN 'PT' THEN 'PERIOD(TIME(' || TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || '))'
WHEN 'PZ' THEN 'PERIOD(TIME(' || TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')' || ' 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 (FORMAT '-(9)9')) END
|| CASE WHEN DecimalFractionalDigits IN (0, -128) THEN '' ELSE ',' || TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) END
|| ')'
WHEN 'A1' THEN COALESCE('SYSUDTLIB.' || ColumnUDTName, '<Unknown> ' || ColumnType)
WHEN 'AN' THEN COALESCE('SYSUDTLIB.' || ColumnUDTName, '<Unknown> ' || ColumnType)

WHEN 'JN' THEN 'JSON(' || TRIM(ColumnLength (FORMAT 'Z(9)9')) || ')'
WHEN 'VA' THEN 'TD_VALIST'
WHEN 'XM' THEN 'XML'

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
;

/*
SELECT
DatabaseName,
TableName,
ColumnName,

DataTypeString
(ColumnType,
ColumnLength,
DecimalTotalDigits,
DecimalFractionalDigits,
CharType,
ColumnUDTName),

ColumnType,
ColumnLength,
DecimalTotalDigits,
DecimalFractionalDigits,
CharType
FROM dbc.ColumnsV
WHERE ColumnType IS NOT NULL
ORDER BY 4
ORDER BY 1,2,3
;
*/
Teradata Employee

Re: Datatype of a column from dbc tables

Hi dnoeth,

 

Can i ask how do you execute the function datatypestring?  I have a problem getting the columns type of my views. 

 

I appreciate your help with this.Thanks!

 

Junior Contributor

Re: Datatype of a column from dbc tables

You can't use it on views as there's no additional metadata besides the column name stored in dbc.

 

Best you can get is HELP db.viewname.*;

Teradata Employee

Re: Datatype of a column from dbc tables

Beginning with Teradata Database 16.0, the DBC.ColumnsQV view also provides information about view columns, in addition to all the information provided by DBC.ColumnsV.

 

There is some additional overhead when querying DBC.ColumnsQV, due to retrieving the view column information, so you should query DBC.ColumnsQV when you know that you also need view column information.

 

If you don't need view column information, then continue to use DBC.ColumnsV.

Enthusiast

Re: Datatype of a column from dbc tables

This is actually a new question, I don't know how to post a new one so posting it here. So, forgive me if youy can. I have something like 'trim(column_name)(varchar(1000))' in my code, I am new to this and have no idea what this is. Please help :)

Junior Contributor

Re: Datatype of a column from dbc tables

Go to the forum where you want to start a new topic and press the big Start a topic button in the upper right :)

 

This is a typecast using old Teradata syntax, similar to cast(trim(column_name) as varchar(1000))