How-To Drop Multiple Table with a naming pattern

Published on
19,615 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 66

Expert Comment

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month