Dropn no.of tables if exisits

Database
Enthusiast

Dropn no.of tables if exisits

Is there a way to Drop 20 tables from a databse if exists. If the table does not exist the script should not error out and should move on in dropping the next table.

my solution:- I tried to insert all the table I need to drop in a temp table and trying to use a for loop to drop all the tables in that temp table.

Any suggestions please.

 

3 REPLIES
Teradata Employee

Re: Dropn no.of tables if exisits

Sounds like you need to write a stored procedure that can consume and process each tablename from the table by executing direct sql and checking the status/error if any.

 

Dave

Enthusiast

Re: Dropn no.of tables if exisits

I am not good at stored procedures.

Any help is grealy appreciated on writing this SP.

Highlighted
Teradata Employee

Re: Dropn no.of tables if exisits

If you have placed all the databasenames/tablenames in a temp table called MyTempTable, you could try something like this:

 

REPLACE PROCEDURE DropTables ( )
DropT:
BEGIN
tLoop:
for tSqlStmt as tSqlStmt cursor for
    select databasename, tablename
    from DBC.TablesV D, MyTempTable M
    where D.DatabaseName = M.DatabaseName
     and  D.TableName = M.TableName
do
    Call DBC.SysExecSQL('Drop Table ' || tSqlStmt.databasename || '.' || tSqlStmt.tablename);
end for tLoop;
END DropT;

 

This joins to TablesV in the data dictionary and so will only drop tables that actually exist.