How-To Drop Multiple Table with a naming pattern

Some weeks ago, I had to drop 1000 temp tables from a DB of my customer, and I didn't want to ctrl-select for 1000 times!!

The names of the tables were starting all with the same prefix, it was "tabx" followed with a 3 digit number, something like Tabx001,Tabx002 and so on.

So I know that quering sys.tables we can obtain a list of the table in a db, and I can use this result to execute the command over the wanted object. So I realized that I had the possibility to write some t-sql code make this job for me.

I used the following logic

1. Get the list of the Table to Drop

We can easily retrieve the table list making a selection in sys.tables using the where condition to filter the result to obtain only the record where name start with Tabx
the following code make it:

SELECT name FROM sys.tables WHERE name like 'Tabx%'

Select all Open in new window

as result of this select I obtain:

Tabx000
Tabx002
.....
Tabx999

2. Compose a Drop Command

For each record of the result of the first step I want to compose the sql Drop Command. I want to have the following results

drop table Tabx000
drop table Tabx001
....
drop table Tabx999

To do that, I can use a cursor to read each record, and for each record store into a varchar variable the "drop table..." command

3. Execute!

For each result of the step 2, I want to execute the command, so I just need to read the value of the variable used to store the command and execute it.

Let's write some code

Before you execute anything I strongly recommend that you take a Full Backup of your database!

I remind you that we are going to DROP some tables, so make sure you are not going to loss any real data! This is potentially very dangerous so check a few times before you start

DECLARE @id varchar(255) -- used to store the table name to drop
DECLARE @dropCommand varchar(255) -- used to store the t-sql command to drop the table
DECLARE @namingPattern varchar(255) -- user to defie the naming pattern of the tables to drop
set @namingPattern = 'Tabx%'

DECLARE tableCursor CURSOR FOR 
    SELECT name FROM sys.tables WHERE name like @namingPattern 

OPEN tableCursor 
FETCH next FROM tableCursor INTO @id 

WHILE @@fetch_status=0 
BEGIN 
	-- Prepare the sql statement
    SET @dropcommand = N'drop table ' + @id 
    -- print @dropCommand -- just a debug check
    -- Execute the drop
    EXECUTE(@dropcommand) 
    
    -- move to next record
    FETCH next FROM tableCursor INTO @id 
END 

CLOSE tableCursor 
DEALLOCATE tableCursor

Select all Open in new window

This code can be used also in a Stored Procedure, and the @namingPattern variable could be an Sp Input Parameter.  That also means that you can schedule the job using the SQL Agent, and you really should build in some security checking to make sure only the target tables are dropped to minimise any widespread disasters.

Once you have a "clean" database then I strongly recommend you create a list of tables that must be kept.

if object_id('usr_keep_tables','U') is null create table usr_keep_tables (name sysname)

insert usr_keep_tables
Select name from sys.tables

-- you can see and if needed manually add or delete entries

select * from usr_keep_tables

Select all Open in new window

and then once you have the "keep" list you can add a where clause or a join to automatically exclude the "keep" files from your cleanup

SELECT name 
FROM   sys.tables 
WHERE  name like @namingPattern
AND    not exists (select null from usr_keep_tables K where k.name = sys.tables.name)

Select all Open in new window

Please ask me if you need some assistance, and report any bug!

Thank's to all.


Gianpiero