Drop all tables whose names begin with a certain string

sql sql-server dynamic-sql
Blorgbeard is out picture Blorgbeard is out · Aug 7, 2008 · Viewed 209.1k times · Source

How can I drop all tables whose names begin with a given string?

I think this can be done with some dynamic SQL and the INFORMATION_SCHEMA tables.

Answer

Curt Hagenlocher picture Curt Hagenlocher · Aug 7, 2008

You may need to modify the query to include the owner if there's more than one in the database.

DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'drop table [' + Table_Name + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'prefix%'

OPEN cmds
WHILE 1 = 1
BEGIN
    FETCH cmds INTO @cmd
    IF @@fetch_status != 0 BREAK
    EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds

This is cleaner than using a two-step approach of generate script plus run. But one advantage of the script generation is that it gives you the chance to review the entirety of what's going to be run before it's actually run.

I know that if I were going to do this against a production database, I'd be as careful as possible.

Edit Code sample fixed.

Related questions

Declare Variable for a Query String

I was wondering if there was a way to do this in MS SQL Server 2005: DECLARE @theDate varchar(60) SET @theDate = '''2010-01-01'' AND ''2010-08-31 23:59:59''' SELECT AdministratorCode, SUM(Total) as theTotal, SUM(WOD.Quantity) as theQty, …

Read More
Getting result of dynamic SQL into a variable for sql-server

Executing dynamic SQL as follows in Stored Procedure: DECLARE @sqlCommand nvarchar(1000) DECLARE @city varchar(75) SET @city = 'London' SET @sqlCommand = 'SELECT COUNT(*) FROM customers WHERE City = @city' EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city How do I use …

Read More
How to use table variable in a dynamic sql statement?

In my stored procedure I declared two table variables on top of my procedure. Now I am trying to use that table variable within a dynamic sql statement but I get this error at the time of execution of that …

Read More