Generate DDL

Tools

Generate DDL

Hello.

I need to create a DDL of all database (only tables and views). Exist any software in the TTU that make DDL?. I tried to make the DDL using the master catalog, but the result doesn't serve to me.

Thank you.
Hernán.
7 REPLIES
N/A

Re: Generate DDL

Execute the results from these two queries.

SELECT 'SHOW TABLE ' || TRIM(DATABASENAME) || '.' ||TRIM(TABLENAME) || ';' FROM DBC.TABLES
WHERE TABLEKIND = 'T';
SELECT 'SHOW VIEW ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ';' FROM DBC.TABLES
WHERE TABLEKIND = 'V';

Re: Generate DDL

Thank TBob !!!
Is a excelent way if you don't have any tool. I used to yesterday

Hernán

Re: Generate DDL

I have programmed a windows GUI tool for this.
It's named DBClone.
This tool can get table/view/trigger/macro/produce/Join index/hash index/’s ddl from teradata database.
And DBClone can save the ddl to file or clipboard.
If you are interested in DBClone, please mail to me.
Max
N/A

Re: Generate DDL

I'm looking for that...!!

The TBob's queries are very good. I've just used the DBC.TABLES.CHILDCOUNT (order by CHILDCOUNT DESC) for obtain the right order of creation...(not exact, but sure better !!! )

But using the SQL Assistant (unfortunately, the only i can use!) i've some various files, one for every file...

Any idea I can obtain fastly only a file ?

Thanks...in advance !!! :-)
Max
N/A

Re: Generate DDL

I found easily how do it...Menu Tools/Options/Export and then checking "Write all exported answer sets to a single file"...

The last problem is some rows like

"Request Text"

and

------------------------------------------------------------------------------

(This not serious...)

after every script table.... :-(

Any idea ?

Thanks
Massimiliano
N/A

Re: Generate DDL

The script above was very helpful.

To export the answer results to a single file, this worked:

1. Tools > Options > Export. 

2. Check "Write all exported answer sets to a single file."

3. File > Export Results.

4. F5 to Execute.

Re: Generate DDL

yes, this is useful but can only generate part of DDL  for too long DDL of talbes or views.