Table definitions

Database
Enthusiast

Table definitions

I was trying to get table definitions for all the tables in a given database and this is the SQL that I used.
//////// sql///////////
select requesttext
from
dbc.tables
where databasename = 'dev_database'
order by 1
///////////// end sql ///////

This worked fine in most cases, but for some tables it Truncated the table definition (because the table definition is huge)

Is there a way to get the complete table definition (other than using show table command).

Thanks
4 REPLIES
Enthusiast

Re: Table definitions

To get the DDL of a Teradata Table, the most affordable way is by issuing a "Show table" SQL statement to a TD database.

So, to get the complete set of DDLs from a database, you'd better generate those "show table" statements with a query like:

SELECT
'show table "' || trim(Databasename) || '"."' || trim(Tablename) || '";' As strSQL
FROM DBC.Tables
WHERE tablekind = 'T'
AND trim(Databasename) = trim(database)


and as a second step, launch the generated statements.

The whole process could be automatized: you could develop one for yourself, or ask for the solution available in the "TD-PS Toolkit" (for which you should probably contact a TD Sales people).
Enthusiast

Re: Table definitions

Thank you for the information.
Enthusiast

Re: Table definitions

Hi Daniel,

Can you pls explain the role of 'As strSQL' in the wuery you have given?
Enthusiast

Re: Table definitions

Its assigning 'As strSQL' as the result set / column name - it can then be referenced in other processes if required