Retrieving all table names and properties using SQL query

[edkuse]

I'm new to using Teradata and was trying desperately to find how to write a SQL query to 1) retrieve a list of all tables or views from a database and 2) retrieve the properties (i.e. column names, data types, constraints) of a table or view.

I know I can simply do this in Queryman, but I need to know how/if I can do this with a SQL statement. In mySQL, I know I can execute what I'm asking here by 1) SHOW tables and 2) DESCRIBE table_name . I'm trying to find the equivalent for Teradata.

Thanks,
Eddie

 

[Patten]

select * from dbc.tables
where databasename = 'yourdb';

select * from dbc.columns
where tablename = 'yourtable'
and databasename = 'yourdb';

A lot of other system information is stored in the dbc database. Just look at all the system tables in dbc, most of them are self-explanatory to know what they do store.

 

[AnilThapak]

Help database yourdatabase: For listing of all database objects,

Help table yourtable: For listing of all table columns,

Show table yourtable: For DDL statement.



Anil

 

[dnoeth]

Check the SQL manual Vol. 4, chapter 4 for more info about HELP/SHOW and Data Dictionary manual for system table info.

Dieter