Show column name and data type in Teradata?
Is there a way to get column name and data type in a Teradata SQL Assistant 15.0view?
In Oracle 11g you can do something like desc tablename and get a script result set of the following :
Name Null Type
test_id NOT NULL NUMBER(19)
test_name VARCHAR2(255)
test_department VARCHAR2(255)
test_year TIMESTAMP(6)
test_external_id NUMBER(10)
So far I've tried help view viewname and the result provides all column names but the values for the data type are all nulls.
Answer
You can use "SHOW VIEW VIEW_NAME" or "HELP COLUMN VIEW_NAME.*" to get all column names and datatype in the view.
Example
HELP COLUMN dbc.Allspace.*
OUTPUT
Column Name Type Nullable Format Max Length
Vproc I2 N -(5)9 2
DatabaseName CF Y X(30) 30
AccountName CF Y X(30) 30
TableName CF Y X(30) 30
MaxPerm F N ---,---,---,---,--9 8
MaxSpool F N ---,---,---,---,--9 8
MaxTemp F N ---,---,---,---,--9 8
CurrentPerm F N ---,---,---,---,--9 8
CurrentSpool F N ---,---,---,---,--9 8
CurrentTemp F N ---,---,---,---,--9 8
PeakPerm F N ---,---,---,---,--9 8
PeakSpool F N ---,---,---,---,--9 8
PeakTemp F N ---,---,---,---,--9 8
MaxProfileSpool F Y ---,---,---,---,--9 8
MaxProfileTemp F Y ---,---,---,---,--9 8