drops on a leaf

SQL Server Drop Tables in Bulk – 2 Methods

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.

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.

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:

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:

Verify the table counts again:

Now we are happy!


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.

Did you find this helpful? Please subscribe!

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

One thought on “SQL Server Drop Tables in Bulk – 2 Methods

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.