All Forums Database
17 Sep 2012
Obtaining DDL Information From The System Tables

 Hi all,
I know that the complete DDL information for a table can be obtained using the show table command, however i
am intrested in knowing where these table,column index info are stored in the system tables for example the datablocksize,
freespace , column is casespecifc , not null  etc basically i would like to trace the entire info available in the show table through
the system tables .
Please help !

dnoeth 4628 posts Joined 11/04
17 Sep 2012

It's impossible to create the full syntax out of the system tables:
Most of the metadata can be accessed using some dbc views like dbc.columnsV and dbc.IndicesV, but some info is not stored within the system tables (only in the table header on each AMP), e.g. DatablockSize and FreeSpace.


21 Sep 2012

So the only way to comapre the DDLs across different servers is to manually obtain the DDl for each table in the database on each of the servers and comapre them manually ?
Is there simpler less time consuming way ?

Qaisar Kiani 337 posts Joined 11/05
22 Sep 2012

If you want to get this information through dbc metadata tables then as Dieter mentioned, it is not possible to get all 'possible' parameter information.
You can get the latest table defintion using SHOW command, and teradata returns the result the table definition in a standard format. So you can probably write some code in an external language and compare the get the table definitions and compare the strings.
I haven't tried this myself, but I guess this should solve your problem!

You must sign in to leave a comment.