In Teradata how do I script out all stored procedures and tables?

Question

I am working on documenting an existing set of databases in a Teradata installation. I am from a SQL Server background. How do I script out all stored procedures and tables in a Teradata DB, like I would in SQL Server, by using the Generate Scripts facility ? I can SHOW individual tables and SPs and I can select a desired list of those objects from dbc.tables, but how do I do the Dynamic SQL to feed that list of names to the SHOW command ? Thanks for your help. JK

Rob Paller Rob Paller · Accepted Answer · 2014-09-10T12:15:25

If you have access to the BTEQ tool you can write SQL to dynamically generate the SHOW commands. This output can be exported from BTEQ to a flat file using the EXPORT command. Then the output file can be fed back into BTEQ and run against the database whose output can be exported to a flat file using an EXPORT command. Clear as mud?

Here is a sample BTEQ script to give you an idea and set you in the right direction. It may not be 100% accurate but it should give you enough to start writing your own script to accomplish the task at hand.

.SET FOLDLINE OFF;
.SET WIDTH 1000;
.SET TITLEDASHES OFF
.OS rm {path to exported SHOW SQL file}
.EXPORT FILE={path to exported SHOW SQL file}

SELECT CASE WHEN T1.TableKind = 'T' 
            THEN 'SHOW TABLE ' || TRIM(T1.DatabaseName) || '.' || TRIM(T1.TableName) || ';'
            WHEN T1.TableKind = 'P'
            THEN 'SHOW PROCEDURE ' || TRIM(T1.DatabaseName) || '.' || TRIM(T1.TableName) || ';'
            /* Repeat for object types your interested in */
            ELSE ''
        END (TITLE '')
  FROM DBC.Tables T1
 WHERE DatabaseName = '{Database}';

.EXPORT RESET

.OS rm {path to SHOW output file}
.EXPORT FILE={path to SHOW output file}

.RUN FILE={path to SHOW SQL file}

.EXPORT RESET

The Teradata documentation found at http://info.teradata.com can help fill the gaps on the TableKind domain and the BTEQ utility and its commands. If you run into problems or have additional questions edit your question above with more information about what you have tried and either I or someone else will be happy to supply additional information.

In Teradata how do I script out all stored procedures and tables?

1 Answers