How-To Drop Multiple Table with a naming pattern

Gianpiero RossiIT Operation Manager
CERTIFIED EXPERT
Published:
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%'

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

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

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)

Open in new window



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

Thank's to all.


Gianpiero
1
19,510 Views
Gianpiero RossiIT Operation Manager
CERTIFIED EXPERT

Comments (1)

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Very handy.  Thanks for writing this.  -Jim

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.