All Forums Database
RichardET 1 post Joined 10/07
04 Oct 2007
SQL to generate a column name list

What is the SQL to return the column list for a particular table and as a followup question, how would one generate a relational list consisting of Database, Tablename, ColumnName, DataType?Thanks!

leo.issac 184 posts Joined 07/06
04 Oct 2007

Use "help table tablename;" to see the list f columns in a table and their datatype information.DBC.columns table would give you information about databasename, tablename, columnaname, datatype and other important information regarding the table columns

hurstrescue 9 posts Joined 09/07
04 Oct 2007

Does this work on views?

dnoeth 3074 posts Joined 11/04
05 Oct 2007

Hi hurstrescue,dbc.columns returns a lot of NULLs for views and HELP VIEW is based on dbc.columns.But "HELP COLUMN viewname.*" is resolved by the PE and thus returns better info.Dieter

Dieter

IrfanAlee 7 posts Joined 03/10
25 Feb 2014

Hi,
I am a bit late but for any one who sees this , this can be of some help
Help table <TableName> -- All columns of a table 
Help columns  <ViewName>.* -- All columns of a table
Show table <TableName> -- DDL of table
Show view <ViewName> -- DDL of View
show select * from  <ViewName>-- DDL of base table in a view
 
Regards,
Irfan

Adeel Chaudhry 730 posts Joined 04/08
25 Feb 2014

True, just one enhancement ....
 
show select * from  <ViewName>-- DDLs of [all the underlying] base table(s)/view(s) in a view

-- If you are stuck at something .... consider it an opportunity to think anew.

goldminer 100 posts Joined 05/09
26 Feb 2014

I use the following in order to get a list of columns names in the order they are defined in the table.  I use it to populate an insert/select when preserving data.
 
sel columnname
from dbc.columns
where databasename='databasename'
and tablename='tablename'
order by columnid;
 
 

david.craig 29 posts Joined 05/13
27 Feb 2014

The columnsV view supports Teradata Database 14.10 extended object names which are longer than 30 characters and contain all Unicode 6.0 BMP characters (e.g. Chinese, Cyrillic, etc.).
sel columnname
from dbc.columnsV
where databasename='databasename'
and tablename='tablename'
order by columnid;
 

goldminer 100 posts Joined 05/09
28 Feb 2014

Thank you for the recommendation Craig!
 
Joe

You must sign in to leave a comment.