DDL for entire database

Database

DDL for entire database

Dear All

I'm new to the Teradata database and have good back ground with DB2 on LUW.

We have  project for Migrating Teradata database from one server to the other. As part of this we have to extract DDL from source for all objects (Tables, View, Roles, Macros etc) and create it on the Target.

Is there a tool like db2look (to generate the DDL for the entire database) in Teradata where in I can get the DDL for entire database?

For tables and views, I was able to use below. However, am stuck with other objects.

sel 'show table ' || databasename   ||'.'||   tablename ||';'  from dbc.tables where databasename = 'dbname' and tablekind = 'T';

sel 'show view ' || databasename   ||'.'||   tablename ||';'  from dbc.tables where databasename = 'dbname' and tablekind = 'V';

Please help.

1 REPLY
Enthusiast

Re: DDL for entire database

1. The following statements would give the DDL's for view and table.

Select Requesttext from dbc.tables where tablekind='V'; -- View.

Select Requesttext from dbc.tables where tablekind='T'; -- Table.

2. However, in cases when the DDL was altered after a create statement , this would only give the latest DDL that was executed.

3. An alternative way is to prepare the show table, show view statements and run it via bteq looping through each line and exporting each command to a file.