A Teradata Show Table Proxy for Full DDL Recreation from DBC
Throughout my recent development activities, an old desire 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, and 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:
Short instruction on how to make use of the query
To apply my code to your environment, you have to make the following decisions and exchange the literal 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 is contained in the order of a DDL statement. The returned lines’ overall order comes from a converted ROWHASH of the TableId on the table level and rank numbers on the column level.
Which table (s)?
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 and how they should look is flexible throughout 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, I could not test a couple of Teradata data types because I lacked empirical examples in my environment. These are many of the PERIOD and INTERVAL families. The issue here is where exactly and in what way 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 or even leave a comment when you have evidence of how it is done.
Second, I cannot guarantee that any Teradata partitioning will be recreated correctly from 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 more profound than this. Also, I had not much variety at hand to try.
Fourth, any Teradata Secondary Indices defined for the table are not covered. They can be incorporated 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.