All Forums Database
seanc 7 posts Joined 09/13
09 Jan 2014
List of all Teradata Column Types with their associated DbTypes

In parsing MetaData from various Teradata servers/databases I have run into a lack of documention that I cannot find and would love to be pointed in the right direction. In the sample database that comes with Teradata all of the columns types are either one or two characters such as "BF" or "D". Now in the application I am writing I need access to all the data types that Teradata supports as well as their DbType.
I have found http://developer.teradata.com/doc/connectivity/ tdnetdp/14.00/webhelp/DataTypeMappings.html which is incredibly helpful. But the problem is that my selects are only showing me the two letter abreviation. So I either need Data Type Mappings for the two letter column types, or I need to have a select that makes the column type come up as the full name.

Raja_KT 1246 posts Joined 07/09
09 Jan 2014

Hi Seanc,
I am not able to understand clearly the requirement. However, with my initial understanding, it seems, you can get details from dbc.columns. select * from dbc.columns. If you want, you can choose only columnname and column type, filtering the databasename as per your wish. Maybe you need to join with other dbc views like dbc.tables etc as per your wish.
However, I see that you are using multi teradata servers and databases. It seems that  Teradata MDS may serve better. My opinion.
Cheers,
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Triton 18 posts Joined 05/08
22 Oct 2014

Here is a list:
A1 ARRAY 
AN MULTI-DIMENSIONAL ARRAY
AT TIME 
BF BYTE
BO BLOB 
BV VARBYTE
CF CHARACTER 
CO CLOB
CV VARCHAR 
D DECIMAL
DA DATE 
DH INTERVAL DAY TO HOUR
DM INTERVAL DAY TO MINUTE 
DS INTERVAL DAY TO SECOND
DY INTERVAL DAY 
F FLOAT
HM INTERVAL HOUR TO MINUTE 
HS INTERVAL HOUR TO SECOND
HR INTERVAL HOUR 
I INTEGER
I1 BYTEINT 
I2 SMALLINT
I8 BIGINT 
JN JSON
MI INTERVAL MINUTE 
MO INTERVAL MONTH
MS INTERVAL MINUTE TO SECOND 
N NUMBER
PD PERIOD(DATE) 
PM PERIOD(TIMESTAMP WITH TIME ZONE)
PS PERIOD(TIMESTAMP) 
PT PERIOD(TIME)
PZ PERIOD(TIME WITH TIME ZONE) 
SC INTERVAL SECOND
SZ TIMESTAMP WITH TIME ZONE 
TS TIMESTAMP
TZ TIME WITH TIME ZONE 
UT UDT Type
XM XML 
YM INTERVAL YEAR TO MONTH
YR INTERVAL YEAR 
++ TD_ANYTYPE

vkache 1 post Joined 06/16
04 Jun 2016

Thanks Triton, that was helpful.

You must sign in to leave a comment.