DWHPro Insights

Teradata DDL from DBC: Legacy Generator and SHOW TABLE

By Roland Wenzlofsky ·

Teradata DDL from DBC: Legacy Generator and SHOW TABLE

Compatibility note: this downloadable generator is retained as a historical example, not a supported replacement for SHOW TABLE. On Teradata Database 20.00.28.81 it returned no DDL for a simple existing test table, while SHOW TABLE returned the definition. Use SHOW TABLE database_name.table_name; for the current table DDL and verify dependent objects separately before recreation.

A historical Teradata DDL generator: capabilities and limitations

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 worked 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:

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

https://www.dwhpro.com/wp-content/uploads/2014/11/TABLE_DDL_OUT_OF_DBC.txt
Short instruction on how to make use of the query

How to Use 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 generator was intended to return ordered DDL lines in THESTRING for one or more tables. Current testing did not establish that functionality. Do not execute generated output without reviewing it against SHOW TABLE and the documented limitations; it is not a complete recreation or backup tool.

Which Database?

Which table(s)?

Use Cases

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

Limitations

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 deeper 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.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

1 thought on “Teradata DDL from DBC: Legacy Generator and SHOW TABLE”

  1. Bro ronald.. it me anurag..how are you..long tym no news..we have met through teradata whats app group…hope u r doing well..

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam