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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
/********************************************** 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
/************************* 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
/******************************** 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:
|
1 2 3 |
--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.
[…] Jeff Mlakar talks about a topic I like—dropping lots and lots of stuff: […]