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 !
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.
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 ?
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!