DDL of the tables in one single query

General

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.

1 REPLY
Senior Apprentice

Re: DDL of the tables in one single query

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) || '";'
FROM DBC.TablesV
WHERE DatabaseName = 'xxxx'
AND Tablekind = 'T'
ORDER BY 1;

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