1

I want this as a query. I tried using a query but that is not giving me proper results. I want to get the datatype of the corresponding columns in Teradata with column name.

I tried with dbc.columntype and column length but I'm getting null/? as a value. Any help would be appreciated. Thanks

Here is the query I used:

Select c.databasename, c.columnname,c.columnlength,c.columntype 
from dbc.columnsV c
Join dbc.tablesV t
on c databasename=t.databasename
zhiguang
  • 345
  • 1
  • 7
Anshu Sam
  • 11
  • 3
  • 1
    DBC.ColumnsV does not contain detailed information for view columns. Use `HELP COLUMN databasename.tablename.*` on an individual view to obtain this information. – Fred Jun 02 '23 at 14:47
  • 1
    Couple of things. 1 - you need to include `c.tablename = t.tablename` in your join (maybe you just missed copying that). 2 - the nulls are for columns in views. Add `where t.tablekind = 'T' ` to your query – Andrew Jun 02 '23 at 14:48
  • @Andrew So is there any way to get the precision and datatype of multiple views? – Anshu Sam Jun 03 '23 at 15:13
  • @Fred So is there any way to get the precision and datatype of multiple views? – Anshu Sam Jun 03 '23 at 15:14
  • Not in a single statement. – Fred Jun 05 '23 at 14:29
  • You can do `HELP COLUMN table_1.*, table_2.*, table_3.*;`, but this is returning a single result set without indicating the view name. – dnoeth Jun 06 '23 at 16:38

1 Answers1

0

I don't understand fully what is the purpose of your join, since all the columns in your select are from c and none for t. Assuming this join is not needed.

SELECT c.DatabaseName, c.ColumnName, c.TableName, c.ColumnLength,c.ColumnType 
FROM DBC.ColumnsV c
WHERE c.DatabaseName = '<The Database you want to query>';

As mentioned in previous answers, the columns that belong to views will appear NULL in this query since views are just a way to view data on tables, so the column Length and Type is defined in the table from which the view derives.

Daniel Herrera
  • 11
  • 3