Stored procedure for dropping multiple tables

Database

Stored procedure for dropping multiple tables

Hello, I am looking to write a stored procedure that when I execute, will drop tables under a certain condition. The reason for this is to drop the temporary tables that SAP leaves behind when a job fails. Purely a testing environment proc, not meant for the customer. Ideally I would like to be able to drop all tables in a schema where the name contains 'LT' 'UV' 'WT', etc. If anyone has an idea or can point me in the right direction, it would be much appreciated

Matt
1 REPLY

Re: Stored procedure for dropping multiple tables

Hi Matt,

Im in the same situation, let me know if you know something. My SSIS package fails and it leaves a bunch of LT,UV,WT tables

so i generated a drop table script

SELECT 'DROP TABLE ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ';'
FROM DBC.TABLES
WHERE DATABASENAME='DBNAME'
AND TABLEKIND = 'T'
AND TABLENAME LIKE '%_UV%';

create a bunch of drop table statements

DROP TABLE DB_MODEL_TEST.SSIS_XXX_LOG;
DROP TABLE DB_MODEL_TEST.SSIS_XXX_UV;
DROP TABLE DB_MODEL_TEST.SSIS_XXX_LOG;
DROP TABLE DB_MODEL_TEST.SSIS_XXX_ET;

execute them all again, if you have an easy way do let me know