A Teradata Show Table Proxy for Full DDL Recreation from DBC
Over the course of my recent development activities, an old desire of mine became relevant again. It goes as follows:
Can one write a query that recreates the entire DDL of a table out of dbc views or tables alone?
Put differently, can I write my Teradata show table proxy that comes closest to what show table does in the background?
Departing from a few building blocks I thankfully found published in Teradata forum entries, I walked my way from top to bottom of a show table output, looked for variations and empirical evidence for every part of a table DDL until I happily arrived at the query you can feel free to download from here:
A short instruction how to make use of the query
To apply my code to your environment, you have to make the following decisions and exchange the literals accordingly:
One or many tables?
The query is designed to return the DDL of one or many tables in sequence and in such a way that you can copy the output of THESTRING as it is returned and run it. A two-step ordering ensures that lines of one table appear together and for every such table, every line contained in the order of a DDL statement. The overall order of returned lines comes from a converted ROWHASH of the TableId on the table level and rank numbers on the column level.
I can imagine the following situations in which the query might be helpful:
- Serial creation of table DDL in one environment for deployment on another one, without backup and recovery involved or in the absence of any backup that covers the current status
- Creation of intermediate tables when how they should look like is flexible over the course of Stored Procedure execution
- Customized partial replication of table structures, such as leaving out any partitions or technical or other columns
- Whenever the Request text in dbc.tables does not contain the create, but the most recent alter table statement
Since the query appears to be quite flexible and powerful to me, there will likely be table definitions that one could define in a Teradata environment but that I cannot recreate from the query result. I am aware of the following limitations to my query:
First, there are a couple of Teradata data types I could not test for lack of empirical examples in my environment. These are many of the PERIOD and INTERVAL family. The issue here is where exactly and in what way does dbc.Columns store numeric settings for such interval types. If you happen to have, them implemented in your environment, feel free to adapt my THE COLUMNS PART area of the query accordingly or even leave a comment when you have evidence how it is done.
Second, I cannot guarantee that any Teradata partitioning will be recreated correctly out of my query. I could successfully test for conventional RANGE_N and a few other patterns
Third, Teradata multilevel partitioning is only preserved up to level 3. I had no empirical testing material at hand to test and develop for anything deeper than this. Also, I had not much variety at hand to test for
Fourth, any Teradata Secondary Indices defined for the table are not covered. They can be incorporated though with reasonable effort by reusing parts of the query.
Fifth, as I am located in Central Europe, I have no firsthand experience with character sets other than Latin or Unicode. Therefore, I cannot guarantee correct DDL recreation for sets other than these