PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

BTEQ script to fetch the DDL’s of table/view/macro

Query 11) BTEQ script to fetch the DDL’s of table/view/macro ?

Answers )

/* 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

Add a Comment

Your email address will not be published. Required fields are marked *