[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


How-To Drop Multiple Table with a naming pattern

Published on
20,397 Points
1 Endorsement
Last Modified:
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:


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%'

    SELECT name FROM sys.tables WHERE name like @namingPattern 

OPEN tableCursor 
FETCH next FROM tableCursor INTO @id 

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

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.

1 Comment
LVL 67

Expert Comment

by:Jim Horn
Very handy.  Thanks for writing this.  -Jim

Featured Post

Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Join & Write a Comment

Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month