Drop Re create Tables / List of tables using a procedure

Database
Highlighted
Enthusiast

Drop Re create Tables / List of tables using a procedure

Hello There,


I have the show tables query as derived below


SELECT 'SHOW TABLE '||TRIM(DATABASENAME)||'.'||TRIM(TABLENAME)||';' AS TBL_INFO
FROM DBC.TABLESV
WHERE DATABASENAME='DB_NAME_XYZ'
AND TABLENAME LIKE 'ABC%'
ORDER BY TABLENAME;


It gives me a list similar to one below that in turn show me the create ddls for the requesed tables

 

TBL_INFO

SHOW TABLE DB_NAME_XYZ.ABC_TABLE_NM_1;
SHOW TABLE DB_NAME_XYZ.ABC_TABLE_NM_2;
SHOW TABLE DB_NAME_XYZ.ABC_TABLE_NM_3;
SHOW TABLE DB_NAME_XYZ.ABC_TABLE_NM_4;


-- Is there a way i can create a Proc that will take 2 argument "$DB_NAME" and LIKE "$TABLE" and iterate the
list mentioned above by inserting a DROP Command prior to CREATE TABLE (SHOW TABLE Command ddl)

 

Like this ---

 

Loop through all values  present in $show_table (

 

DROP TABLE TABLE DB_NAME_XYZ.ABC_TABLE_NM_1;
CREATE TABLE (SHOW TABLE ABC_TABLE_NM_1);


DROP TABLE TABLE DB_NAME_XYZ.ABC_TABLE_NM_2;
CREATE TABLE (SHOW TABLE ABC_TABLE_NM_2);

 

DROP TABLE TABLE DB_NAME_XYZ.ABC_TABLE_NM_3;
CREATE TABLE (SHOW TABLE ABC_TABLE_NM_3);


DROP TABLE TABLE DB_NAME_XYZ.ABC_TABLE_NM_4;
CREATE TABLE (SHOW TABLE ABC_TABLE_NM_4);
)

 

Any direction and pointers will be highly appriciated

 

Thank You,

 

 

 


Accepted Solutions
Teradata Employee

Re: Drop Re create Tables / List of tables using a procedure

Slight correction to the code:

echo 's/CREATE  *[^ ]* *TABLE  *\([^ ]*\) /DROP TABLE \1;\n&/' >add_drop.sed
echo '/^[ -]*$/d' >>add_drop.sed
bteq <<EOF1
.logon ******** Your logon here ***********
.export report file=draft1.btq
SELECT 'SHOW TABLE '||TRIM(DATABASENAME)||'.'||TRIM(TABLENAME)||';' AS TBL_INFO
FROM DBC.TABLESV
WHERE DATABASENAME='DB_NAME_XYZ'
AND TABLENAME LIKE 'ABC%'
AND TABLEKIND = 'T'
ORDER BY TABLENAME;
.export reset
.os tail +2 draft1.btq > draft2.btq
.export report file=draft3.btq
.run file=draft2.btq
.export reset
.os sed -fadd_drop.sed draft3.btq > dropcreate.btq
.os rm draft[1-3].btq
.quit
EOF1
rm add_drop.sed
1 ACCEPTED SOLUTION
5 REPLIES 5
Ambassador

Re: Drop Re create Tables / List of tables using a procedure

SHOW can't be used in a Stored Procedure, but you might try the workaround shown in Running Unsupported Queries from a Stored Procedure.

Enthusiast

Re: Drop Re create Tables / List of tables using a procedure

Thanks for the pointer, Seems like it is using Java, is there any other option / articles that use BTEQ to do the same as export the "SHOW TABLE" into a file and execute them in loop for all ddls as BTEQ script. i can try some. i can aslo insert DROP TABLE statements for each file/table as a unix script but i am looking to get the create ddl for each table. any direction will be helpful

 

Thank You,

 

Teradata Employee

Re: Drop Re create Tables / List of tables using a procedure

Here is a shell script that will create a file called "dropcreate.btq."  Take a look at that file, and if it looks okay then run it.

echo 's/CREATE  *[^ ]* *TABLE  *\([^ ]*\) /DROP TABLE \1;\n&/' >add_drop.sed
echo '/^[ -]*$/d' >>add_drop.sed
bteq <<EOF1
.logon ******** Your logon here ***********
.export report file=draft1.btq
SELECT 'SHOW TABLE '||TRIM(DATABASENAME)||'.'||TRIM(TABLENAME)||';' AS TBL_INFO
FROM DBC.TABLESV
WHERE DATABASENAME='DB_NAME_XYZ'
AND TABLENAME LIKE 'ABC%'
AND TABLEKIND = 'T'
ORDER BY TABLENAME;
.export reset
.os tail +2 draft1.btq > draft2.btq
.export report file=draft3.btq
.run file=draft2.btq
.export reset
.os sed -fdc.sed draft3.btq > dropcreate.btq
.os rm draft[1-3].btq
.quit
EOF1
rm add_drop.sed
Teradata Employee

Re: Drop Re create Tables / List of tables using a procedure

Slight correction to the code:

echo 's/CREATE  *[^ ]* *TABLE  *\([^ ]*\) /DROP TABLE \1;\n&/' >add_drop.sed
echo '/^[ -]*$/d' >>add_drop.sed
bteq <<EOF1
.logon ******** Your logon here ***********
.export report file=draft1.btq
SELECT 'SHOW TABLE '||TRIM(DATABASENAME)||'.'||TRIM(TABLENAME)||';' AS TBL_INFO
FROM DBC.TABLESV
WHERE DATABASENAME='DB_NAME_XYZ'
AND TABLENAME LIKE 'ABC%'
AND TABLEKIND = 'T'
ORDER BY TABLENAME;
.export reset
.os tail +2 draft1.btq > draft2.btq
.export report file=draft3.btq
.run file=draft2.btq
.export reset
.os sed -fadd_drop.sed draft3.btq > dropcreate.btq
.os rm draft[1-3].btq
.quit
EOF1
rm add_drop.sed
Enthusiast

Re: Drop Re create Tables / List of tables using a procedure

Excellent Sir !!!.

The problem has been resolved, i use the BTEQ way

 

Best Regards