Conditional DROP TABLE

Database
Enthusiast

Conditional DROP TABLE

Does anyone know the syntax of drop table only when it exists?
2 REPLIES
Enthusiast

Re: Conditional DROP TABLE

As always many roads lead to Rom... ;-)

Here are two:

1.)
If you do this in a BTEQ script you could simple reduce the severity of the table does not exist error (don't have the number in my head) and drop it to a warning. Once you are done with your drops, restore the previous severity.

That way it does not matter if the table exists or not.

2.)
If you do this for many tables you can also generate the SQL for the drops out of the dbc tables. If a table does not exist, it will simply not get included in the generate SQL as the dbc tables would not contain records for this table.
Enthusiast

Re: Conditional DROP TABLE

You can accomplish by using a BTEQ script

.logon TPID/Uid,PWD;

SEL
DatabaseName
,TableName
FROM DBC.Tables
WHERE DatabaseName = 'dw_support_Db'
AND TableName = 'test';

.IF ACTIVITYCOUNT > 0 THEN DROP TABLE dw_support_db.test;

.LOGOFF;

.QUIT;

Vinay