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.
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.
If you have placed all the databasenames/tablenames in a temp table called MyTempTable, you could try something like this:
REPLACE PROCEDURE DropTables ( )
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
Call DBC.SysExecSQL('Drop Table ' || tSqlStmt.databasename || '.' || tSqlStmt.tablename);
end for tLoop;
This joins to TablesV in the data dictionary and so will only drop tables that actually exist.