List of all Teradata Column Types with their associated DbTypes

Database
Enthusiast

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.

3 REPLIES
Enthusiast

Re: List of all Teradata Column Types with their associated DbTypes

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,

Teradata Employee

Re: List of all Teradata Column Types with their associated DbTypes

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

N/A

Re: List of all Teradata Column Types with their associated DbTypes

Thanks Triton, that was helpful.