There are times when you may find yourself needing to drop many tables in your database but not all. Some criteria defines what must go. It could be archived tables, tables before or after a certain date time stamp, dummy data tables, etc.

Let’s look at 2 simple methods for dropping lots of tables in your database.

Feature Image / License

Drop Tables In SQL Server

Let’s assume that you have lots of tables that need to be dropped according to some criteria. Trying to do them all at once isn’t a good idea. Even with a powerful server it will either take forever or simply never finish.

For example – you may have millions of tables in sys.tables or millions of indexes you need to drop. SQL Server won’t process them well if you try to run it as one big statement.

/**********************************************
	THIS IS THE WAY NOT TO DO THINGS
***********************************************/
--just a snippet...
declare @sql nvarchar(max) = '';
select @sql += 'drop table ' + QUOTENAME([name]) + char(13) + char(10) --carriage return, line feed
from sys.tables
where [name] like 'MyTable_%';

--print @sql;
exec sp_executesql @sql;

Although this is a small contrived example, if you are trying to drop millions of objects at once like this on a busy system, performance will be bad.

Now let’s look at 2 better ways to tackle the problem. The first will be a cursor and the seconds batching with dynamic sql. But first the preparation…

Preparing the Demo

First we need to have a large amount of tables. I am using the AdventureWorks2016 sample database. Let’s first create a bunch of tables having a common suffix.

USE AdventureWorks2016;
GO

select * from sys.tables order by [name]; --75

--stage some tables to drop
declare @rc int;
set @rc = 0;

declare @sql nvarchar(max);
set @sql = '';

while (@rc <= 10000)
begin
	select @sql = '
	create table MyTable_' + cast(@rc as varchar) +  '(
		col1 int,
		col2 char(10)
		)'

	--print @sql;
	exec sp_executesql @sql;
	set @rc += 1;
end

select * from sys.tables order by [name]; --10,076
select * from sys.tables where [name] like 'MyTable_%'; --10,001

Now we have about 10,000 sample tables to drop.

NOTE: if you really want to crank up the heat then make this 10 million tables on a weak server / desktop / laptop running SQL Server.

Dropping Tons of Tables with a Cursor

Here is an example of dropping many tables with a cursor:

/*************************
	METHOD 1: CURSOR
**************************/

--load working table
IF OBJECT_ID('tempdb..#working') IS NOT NULL
BEGIN
	DROP TABLE #working;
END
GO

SELECT [name] AS 'tblname' -- + '_testing' AS 'tblname'
INTO #working
FROM sys.tables
WHERE [name] LIKE 'MyTable_%';

SELECT * FROM #working; --10,001

--start drops
DECLARE @tableName VARCHAR(128)
DECLARE @sql NVARCHAR(MAX);
SET @sql = '';

DECLARE curDropTables CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT [tblname] FROM #working
OPEN curDropTables
FETCH NEXT FROM curDropTables INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN 
    --PRINT @tableName    
    SELECT @sql = 'drop table [' + @tableName + ']' --can also use QUOTENAME()
    PRINT @sql;
    EXEC sp_executesql @sql;
    SET @sql = '';
    FETCH NEXT FROM curDropTables INTO @tableName
END
CLOSE curDropTables
DEALLOCATE curDropTables

--verify drops
select * from sys.tables order by [name]; --75
select * from sys.tables where [name] like 'MyTable_%'; --0

As you can see, we first load a temp table with the definition of what we want to iterate through. Then we load each row of that result set into a variable inside the cursor. By using dynamic sql we can craft the drop statements we need to do the work. Lastly, we verify the drop counts.

Dropping Tons of Tables with Dynamic SQL in a Loop

Let’s stage the tables again so we can do another example. Here is one way to do it with loops and dynamic sql:

/********************************
	METHOD 2: BATCHING LOOPS
*********************************/

--Create the tables again and load the working tbl
SELECT * FROM #working; --10,001

--Drop in batches
declare @rc int; --record count
set @rc = 1;

declare @sql nvarchar(max);
set @sql = '';

while @rc > 0
begin
	--drop by sets of 1,000 - the top(x) can be a variable and dynamic sql
	--PROTIP: make sure you use += and not just = when assigning the @sql variable
	select top 100 @sql += 'drop table ' + QUOTENAME([name]) + char(13) + char(10) --carriage return, line feed
	from sys.tables
	where [name] like 'MyTable_%';

	select @rc = @@ROWCOUNT;
	
	--print @sql;
	exec sp_executesql @sql;
end

Verify the table counts again:

--verify drops
select * from sys.tables order by [name]; --75
select * from sys.tables where [name] like 'MyTable_%'; --0

Now we are happy!

Caveats

There are some gotchas to be aware of here. The examples I demonostrate above are relatively simple; however, with a less straightforward schema you have to consider other things too.

Foreign keys will throw a monkey wrench in your efforts. This dependency introduces a sequence that we must use, i.e. we have to drop one set of tables before we can drop another – try it out! Easier might be to just drop those objects first using the same methodology and then finish with the rest.

Thanks for reading!

---

If you liked this post then you might also like my recent post about Using T-SQL to Insert, Update, Delete Millions of Rows.

Do you care about InfoSec and Privacy? Then YOU need to use a VPN.