Generate a database ddl in Teradata v14

Data Modeling
Fan

Generate a database ddl in Teradata v14

I need to reverse engineer several Teradata 14 databases into ERwin 9.5. I have researched and watched the ERwin video on reverse engineering a Teradata v14.0 database. As my company has 100's on databases in one place, the task is impossible as I only want to reverse a few databases at this time.  The video did discuss selecting the Teradata database ddl script file.  The DBAs have informed me that they do not have ddl files for the databases.

My question is, is their a way to generate the ddl of a full database?  I have googled this and found some info on earlier versions of Teradata.  I wanted to post this just in case there is a different method for this version. I have access to SQL Assistant and I need to have a full database ddl.  Not to get off topic, as this is a Teradata forum, but if anyone has reverse engineered a Teradata database in ERwin 9.5 by selecting "Database" as opposed to "Script File", please let me know how you did it.  I connect to Teradata perfectly. There isn't a way to just pick a single database to reverse engineer. I did try to type in the name of one database in the "Database Name" field when connecting from ERwin.  I receive nothing but ERwin Error 105 messages, then additional error messages.

Thank you for your assistance!

2 REPLIES
Fan

Re: Generate a database ddl in Teradata v14

Update:  I did figure out how to create a .ddl file.  I right clicked and did a "SHOW DEFINITION" for each table in the database for which I wanted to reverse engineer.  I copied each table definition into Word.  The last table definition was saved as "databasename.ddl" (File -> Save As).  I then opened the .ddl file in the Teradata SQL Assistant Query window and copied over the other table definitions preceeding the last one into the Query window.  I then saved all the table definitions as the "databasename.ddl" - replacing the earlier one.

The next problem though is that when I went to ERwin 9.5 to reverse engineer this script file, it didn't work.  More error messages...

However, for creating a .ddl file, this is what "seemed" to work for me.

Junior Contributor

Re: Generate a database ddl in Teradata v14

I can't help you with your ERwin issue, but you can get all DDLs without too much cut'n'paste :-)

Open Tools -> Options -> Export and set:

Write all exported answer sets to a single file -> checked

Write the SQL and result status to the export file -> unchecked

Write column headers to the export file -> unchecked

Replace Carriage Returns in column data with blanks -> unchecked

Then run following SQL:

select 'show table ' || databasename || '.' || tablename || ';'
from dbc.TablesV
where Tablekind = 'T'
and databasename in ('mydb', 'mydb2', 'mydb3', ...)
order by 1;

Finally cut & paste the result to a query window, set File -> Export Results, press F5 and choose a txt file when prompted.