Obtaining a Create Table Text String Using the DBC Only.

Database

Obtaining a Create Table Text String Using the DBC Only.

Ok, Here is my problem. I need a single text string with the equivalent of what is in the DBC.tables.RequestText. However, as we all know the RequestText becomes useless when an alter table statement is issued.

Up to now I have been using the following to roughly reconstruct the create table statement (It is a work in progress) But there has got to be a better way. And in my case Show Table is out of the question.

So, my question is

1. Is there an easier place to get the create table text? So other DBC view I am not aware of?
2. Is there a more efficient way of doing the following (The recursive SQL is a bit slow)

DROP TABLE .TABLECOLS;

CREATE TABLE .TABLECOLS
( DATABASENAME VARCHAR(30)
, TABLENAME VARCHAR(30)
, COLUMNSTRING VARCHAR(2000)
) PRIMARY INDEX (DATABASENAME, TABLENAME)
;

DROP TABLE .TABLEINDICES;

CREATE TABLE .TABLEINDICES
( DATABASENAME VARCHAR(30)
, TABLENAME VARCHAR(30)
, INDEXTYPE VARCHAR(30)
, INDEXSTRING VARCHAR(2000)
) PRIMARY INDEX (DATABASENAME, TABLENAME)
;

INSERT INTO .TABLECOLS
( DATABASENAME
, TABLENAME
, COLUMNSTRING
)
WITH RECURSIVE DBCOLUMNS (DATABASENAME,
TABLENAME,
COLUMNNAME,
COLUMNID)
AS
( SELECT c.DATABASENAME
, c.TABLENAME
, CASE WHEN c.COLUMNTYPE = 'AT'
THEN TRIM(c.COLUMNNAME)||' TIME'
WHEN c.COLUMNTYPE = 'BO'
THEN TRIM(c.COLUMNNAME)||' BLOB'
WHEN c.COLUMNTYPE = 'BF'
THEN TRIM(c.COLUMNNAME)||' BYTE'
WHEN c.COLUMNTYPE = 'BV'
THEN TRIM(c.COLUMNNAME)||' VARBYTE'
WHEN c.COLUMNTYPE = 'CF'
THEN TRIM(c.COLUMNNAME)||' CHAR'
WHEN c.COLUMNTYPE = 'CO'
THEN TRIM(c.COLUMNNAME)||' CLOB'
WHEN c.COLUMNTYPE = 'CV'
THEN TRIM(c.COLUMNNAME)||' VARCHAR'
WHEN c.COLUMNTYPE = 'D'
THEN TRIM(c.COLUMNNAME)||' DECIMAL'
WHEN c.COLUMNTYPE = 'DA'
THEN TRIM(c.COLUMNNAME)||' DATE'
WHEN c.COLUMNTYPE = 'F'
THEN TRIM(c.COLUMNNAME)||' FLOAT'
WHEN c.COLUMNTYPE = 'I'
THEN TRIM(c.COLUMNNAME)||' INTEGER'
WHEN c.COLUMNTYPE = 'I1'
THEN TRIM(c.COLUMNNAME)||' BYTEINT'
WHEN c.COLUMNTYPE = 'I2'
THEN TRIM(c.COLUMNNAME)||' SMALLINT'
WHEN c.COLUMNTYPE = 'TS'
THEN TRIM(c.COLUMNNAME)||' TIMESTAMP'
END||
CASE WHEN c.COLUMNTYPE IN ('BV','CF','CV')
THEN '('||TRIM(c.COLUMNLENGTH)||') '
WHEN c.COLUMNTYPE IN ('D')
THEN '('||TRIM(c.DECIMALTOTALDIGITS)||','||TRIM(c.DECIMALFRACTIONALDIGITS)||') '
ELSE ''
END||
CASE WHEN c.COLUMNTYPE IN ('DA','TS')
THEN 'FORMAT'' '||TRIM(c.COLUMNFORMAT)||''' '
ELSE ''
END||
CASE WHEN c.NULLABLE='N'
THEN 'NOT NULL '
ELSE ''
END||
CASE WHEN c.COMPRESSVALUELIST IS NOT NULL
THEN 'COMPRESS '||TRIM(c.COMPRESSVALUELIST)||' '
ELSE ''
END (VARCHAR(2000)) AS COLUMNNAME
, c.COLUMNID
FROM DBC.COLUMNS c
INNER JOIN DBC.TABLES t
ON c.TABLENAME = t.TABLENAME
AND c.DATABASENAME = t.DATABASENAME
WHERE t.TABLEKIND = 'T'
AND c.DATABASENAME =
QUALIFY ROW_NUMBER () OVER(PARTITION BY c.DATABASENAME, c.TABLENAME ORDER BY c.COLUMNID) = 1
UNION ALL
SELECT c2.DATABASENAME
, c2.TABLENAME
, x.COLUMNNAME || ', ' ||
CASE WHEN c2.COLUMNTYPE = 'AT'
THEN TRIM(c2.COLUMNNAME)||' TIME'
WHEN c2.COLUMNTYPE = 'BO'
THEN TRIM(c2.COLUMNNAME)||' BLOB'
WHEN c2.COLUMNTYPE = 'BF'
THEN TRIM(c2.COLUMNNAME)||' BYTE'
WHEN c2.COLUMNTYPE = 'BV'
THEN TRIM(c2.COLUMNNAME)||' VARBYTE'
WHEN c2.COLUMNTYPE = 'CF'
THEN TRIM(c2.COLUMNNAME)||' CHAR'
WHEN c2.COLUMNTYPE = 'CO'
THEN TRIM(c2.COLUMNNAME)||' CLOB'
WHEN c2.COLUMNTYPE = 'CV'
THEN TRIM(c2.COLUMNNAME)||' VARCHAR'
WHEN c2.COLUMNTYPE = 'D'
THEN TRIM(c2.COLUMNNAME)||' DECIMAL'
WHEN c2.COLUMNTYPE = 'DA'
THEN TRIM(c2.COLUMNNAME)||' DATE'
WHEN c2.COLUMNTYPE = 'F'
THEN TRIM(c2.COLUMNNAME)||' FLOAT'
WHEN c2.COLUMNTYPE = 'I'
THEN TRIM(c2.COLUMNNAME)||' INTEGER'
WHEN c2.COLUMNTYPE = 'I1'
THEN TRIM(c2.COLUMNNAME)||' BYTEINT'
WHEN c2.COLUMNTYPE = 'I2'
THEN TRIM(c2.COLUMNNAME)||' SMALLINT'
WHEN c2.COLUMNTYPE = 'TS'
THEN TRIM(c2.COLUMNNAME)||' TIMESTAMP'
END||
CASE WHEN c2.COLUMNTYPE IN ('BV','CF','CV')
THEN '('||TRIM(c2.COLUMNLENGTH)||') '
WHEN c2.COLUMNTYPE IN ('D')
THEN '('||TRIM(c2.DECIMALTOTALDIGITS)||','||TRIM(c2.DECIMALFRACTIONALDIGITS)||') '
ELSE ''
END||
CASE WHEN c2.COLUMNTYPE IN ('DA','TS')
THEN 'FORMAT'' '||TRIM(c2.COLUMNFORMAT)||''' '
ELSE ''
END||
CASE WHEN c2.NULLABLE='N'
THEN 'NOT NULL '
ELSE ''
END||
CASE WHEN c2.COMPRESSVALUELIST IS NOT NULL
THEN 'COMPRESS '||TRIM(c2.COMPRESSVALUELIST)||' '
ELSE ''
END (VARCHAR(2000)) AS COLUMNNAME
, c2.COLUMNID
FROM DBC.COLUMNS c2
INNER JOIN DBC.TABLES t2
ON c2.TABLENAME = t2.TABLENAME
AND c2.DATABASENAME = t2.DATABASENAME
INNER JOIN DBCOLUMNS x
ON c2.COLUMNID = x.COLUMNID + 1
AND c2.DATABASENAME = x.DATABASENAME
AND c2.TABLENAME = x.TABLENAME
WHERE t2.TABLEKIND = 'T'
AND c2.DATABASENAME =
)
SELECT d.DATABASENAME,
d.TABLENAME,
d.COLUMNNAME
FROM DBCOLUMNS d
INNER JOIN (SELECT DATABASENAME
, TABLENAME
, MAX(COLUMNID) AS MCOLUMNID
FROM DBCOLUMNS
GROUP BY 1,2) m
ON d.DATABASENAME = m.DATABASENAME
AND d.TABLENAME = m.TABLENAME
AND d.COLUMNID = m.MCOLUMNID
;

INSERT INTO .TABLEINDICES
( DATABASENAME
, TABLENAME
, INDEXTYPE
, INDEXSTRING
)
WITH RECURSIVE DBINDICES (DATABASENAME,
TABLENAME,
INDEXTYPE,
COLUMNNAME,
COLUMNPOSITION)
AS
( SELECT i.DATABASENAME
, i.TABLENAME
, CASE WHEN i.UNIQUEFLAG = 'Y'
THEN 'UNIQUE PRIMARY INDEX'
ELSE 'PRIMARY INDEX'
END AS INDEXTYPE
, i.COLUMNNAME (VARCHAR(2000)) AS COLUMNNAME
, i.COLUMNPOSITION
FROM DBC.INDICES i
INNER JOIN DBC.TABLES t
ON i.TABLENAME = t.TABLENAME
AND i.DATABASENAME = t.DATABASENAME
WHERE i.INDEXTYPE = 'P'
AND i.DATABASENAME =
UNION ALL
SELECT i2.DATABASENAME
, i2.TABLENAME
, CASE WHEN i2.UNIQUEFLAG = 'Y'
THEN 'UNIQUE PRIMARY'
ELSE 'PRIMARY'
END AS INDEXTYPE
, x.COLUMNNAME || ',' ||
i2.COLUMNNAME
, i2.COLUMNPOSITION
FROM DBC.INDICES i2
INNER JOIN DBC.TABLES t2
ON i2.TABLENAME = t2.TABLENAME
AND i2.DATABASENAME = t2.DATABASENAME
INNER JOIN DBINDICES x
ON i2.COLUMNPOSITION = x.COLUMNPOSITION + 1
AND i2.DATABASENAME = x.DATABASENAME
AND i2.TABLENAME = x.TABLENAME
WHERE i2.INDEXTYPE = 'P'
AND i2.DATABASENAME =
)
SELECT DATABASENAME
, TABLENAME
, INDEXTYPE
, COLUMNNAME
FROM DBINDICES i
INNER JOIN (SELECT DATABASENAME
, TABLENAME
, MAX(COLUMNPOSITION)AS MCOLUMNPOSITION
FROM DBINDICES
GROUP BY 1,2) m
ON i.DATABASENAME = m.DATABASENAME
AND i.TABLENAME = m.TABLENAME
AND i.COLUMNPOSITION = m.MCOLUMNPOSITION
;

SELECT 'CREATE TABLE '||TRIM(t.DATABASENAME)||'.'||TRIM(t.TABLENAME)||'('||
t.COLUMNSTRING||') '||
i.INDEXTYPE||' ('||
i.INDEXSTRING||');'
FROM .TABLECOLS t
INNER JOIN .TABLEINDICES i
ON t.DATABASENAME = i.DATABASENAME
AND t.TABLENAME = i.TABLENAME
;
3 REPLIES

Re: Obtaining a Create Table Text String Using the DBC Only.

Have you ever looked at the RequestText column in DBC.Tables

select RequestText
from DBC.Tables
where DatabaseName = 'xxxxxxx' and
TableName = 'yyyyyyyy'

should give you the create statement

hope this helps
Enthusiast

Re: Obtaining a Create Table Text String Using the DBC Only.

What is the purpose of you creating the DDL?

If you want to extract the DDLs of a particular db to a file, you can do the following.

SELECT DATABASENAME, TABLENAME, 'SHOW TABLE ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ';'
FROM DBC.TABLES
WHERE DATABASENAME = 'YourDB'
AND TABLEKIND = 'T' -- Only Tables
ORDER BY 1,2;

Executing above query would give us the show table query for all the tables in the third column like below .

SHOW TABLE YourDB.Table1;
SHOW TABLE YourDB.Table2;
SHOW TABLE YourDB.Table3;
....

Copy the show table queries from the third column and Execute all the show table queries at one shot and export the results in a txt file format in the needed location(local drive). (SQL Assistant -> File -> Export Results)

The Result will be written in to a separate file with a naming convention as SQLAExport.txt

If you need the result to be in multiple files then uncheck the option ‘Write all exported answer sets to a single file’ in the tools\option\Export\import table.

Enthusiast

Re: Obtaining a Create Table Text String Using the DBC Only.

Another option based on Toad's recommendation is to run the SQL using BTEQ and export the results to a flat file. Your flat file will contain all of the SHOW TABLE commands. Afterward you can turn around and use the BTEQ RUN command to execute the file you just created. This too can be exported to a file and then you have all of the information you want in a single flat file. You may need to build the EXPORT commands in a SELECT statement in the outer most BTEQ prior to and immediately after the SELECT statement that creates the SHOW TABLE statements.

I do like the recursive SQL solution you have created. Have you considered placing it in a macro that allows you to build the DDL a database at a time using the parameters from the macro to drive the recursion. This might yield you slightly better performance if you just want to run one database at a time along with some flexibility.