corresponding teradata query for a oracle query

Database
N/A

corresponding teradata query for a oracle query

in oracle we have

desc "table name" for viewing table description.

whats the query for the same purpose in teradata?
5 REPLIES
Teradata Employee

Re: corresponding teradata query for a oracle query

Hello,

In Teradata, you can list the table definition using SHOW TABLE command. For example...."SHOW TABLE Table1;".

HTH!

Regards,

Adeel

Enthusiast

Re: corresponding teradata query for a oracle query

The following sql will give you the DDL for the table :

show table tablename ;

The following will give you the table description :

help table tablename;

Hope this helps !!
Enthusiast

Re: corresponding teradata query for a oracle query

The Help Table works, but provides a less-readable version one table at a time. I use the following to generate similar results for several tables at once (or a whole database) in more readable format. It pulls from the same DBC.Columns like Help Table does:

Select DatabaseName, TableName, ColumnName,
(case
when columntype='CV' then 'VARCHAR'
when columntype='DA' then 'DATE'
when columntype='I' then 'INTEGER'
when columntype='CF' then 'CHAR'
when columntype='F' then 'FLOAT'
when columntype='I2' then 'SMALLINT'
when columntype='BO' then 'BLOB'
when columntype='CO' then 'CLOB'
when columntype='TS' then 'TIMESTAMP'
when columntype='BF' then 'BYTE'
when columntype='I1' then 'BYTEINT'
when columntype='SZ' then 'TIMESTAMP W/ZONE'
when columntype='BV' then 'VARBYTE'
when columntype='DM' then 'INVERVAL DAY TO MINUTE'
when columntype='D' then 'DECIMAL'
else columntype end) as Datatype,
(case
when DecimalTotalDigits is null then ColumnLength
else DecimalTotalDigits||','||DecimalFractionaldigits
end) as col_length,
(case when Nullable='N' then 'NOT NULL'
else null
end) as Nullable,
(case when ColumnType in ('CO','BO','CV','CF','I','I1','I2') then null
else ColumnFormat
end) as ColumnFormat,
DefaultValue,
ColumnId
From DBC.Columns
where databasename = '_yourdbname_' and tablename like '%_yourtablecriteria_%'
order by databasename, tablename, columnid

Re: corresponding teradata query for a oracle query

What information does the "tvfields.MaxLength" column provide in the dbc.columns table ?

Enthusiast

Re: corresponding teradata query for a oracle query

Tells the ColumnLength...