All Forums General
deepakram 1 post Joined 01/15
06 Apr 2015
DDL of the tables in one single query

Is there a way that I can extract the DDL of all the tables in one single query.

select DatabaseName,TableName,RequestText from db.tables;
gives the database name , table name and the last DDL on the table. But is it possible to get DDL to create the tables in form of resultset to a query instead of querying show db.tableName several times
Also is there a way to write a function or a stored procedure to pass the params from query which could return the result of "show db.tableName " as a column in the resultset.
Any other alternate approach is also welcomed as long as resultset contains tablename,DDL as rows.

dnoeth 4628 posts Joined 11/04
07 Apr 2015

You can't get the CREATE as a single row because it might be more than the maximum VarChar size (or you want it as a CLOB).
Plus RequestText is not reliable for tables, you can only create all the SHOWs using SQL like this:

SELECT 'SHOW TABLE "' || TRIM(Databasename) || '"."' || TRIM(Tablename) || '";'
WHERE DatabaseName = 'xxxx'
  AND Tablekind = 'T'

And then run the result. This is usually done as a BTEQ script...
Regarding the result of a SHOW as a function, have a look at Glenn McCall's article how to solve this:
Running Unsupported Queries from a Stored Procedure


You must sign in to leave a comment.