SQL to generate a column name list

Database
Highlighted

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!
8 REPLIES
Enthusiast

Re: SQL to generate a column name list

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
Enthusiast

Re: SQL to generate a column name list

Does this work on views?
Senior Supporter

Re: SQL to generate a column name list

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
Enthusiast

Re: SQL to generate a column name list

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


Enthusiast

Re: SQL to generate a column name list

True, just one enhancement ....


 


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


Enthusiast

Re: SQL to generate a column name list

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;


 


 


Teradata Employee

Re: SQL to generate a column name list

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;

 


Enthusiast

Re: SQL to generate a column name list

Thank you for the recommendation Craig!


 


Joe