Obtaining DDL Information From The System Tables

Database
Enthusiast

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 !

3 REPLIES
Senior Apprentice

Re: Obtaining DDL Information From The System Tables

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.

Dieter

Enthusiast

Re: Obtaining DDL Information From The System Tables

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 ?

Thanks,

Rajeev

Enthusiast

Re: Obtaining DDL Information From The System Tables

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!