BTEQ script to fetch table DDL's

/* This BTEQ script fetches the table DDL's for the current DB */
/* Make sure you set the database before running the script */
/* Copy contents and paste it in a text file (eg getDDL.scp) */
/* in the prompt # bteq < getDDL.scp (to execute) */

.SET SIDETITLES OFF
.SET WIDTH 254
.SET QUIET ON

.os IF EXIST showDDL.tmp del showDDL.tmp

.EXPORT REPORT file=showDDL.tmp

SELECT '.SET TITLEDASHES OFF' (TITLE '');

/* Generate Table DDL's */

SELECT 'SHOW TABLE '||TABLENAME||';' (TITLE '')
FROM DBC.TABLES
WHERE DATABASENAME = DATABASE AND TABLEKIND = 'T'
ORDER BY 1;

/* Generate Views DDL's */
SELECT 'SHOW VIEW '||TABLENAME||';' (TITLE '')
FROM DBC.TABLES
WHERE DATABASENAME = DATABASE AND TABLEKIND = 'V'
ORDER BY 1;


/* Generate Macro DDL's */
SELECT 'SHOW MACRO '||TABLENAME||';' (TITLE '')
FROM DBC.TABLES
WHERE DATABASENAME = DATABASE AND TABLEKIND = 'M'
ORDER BY 1;


/* Generate Stored Procedure DDL's */
SELECT 'SHOW PROCEDURE '||TABLENAME||';' (TITLE '')
FROM DBC.TABLES
WHERE DATABASENAME = DATABASE AND TABLEKIND = 'P'
ORDER BY 1;


SELECT '.DEFAULTS' (TITLE '');

SELECT '.EXPORT RESET' (TITLE '');


.EXPORT RESET

.os IF EXIST DDLS.txt del DDLS.txt

.EXPORT REPORT file=DDLS.txt

.run file = showDDL.tmp

.os IF EXIST showDDL.tmp del showDDL.tmp/Q