CODEWITHSUNDEEP
sqlsql-server
janith
janith

Reputation: 115

Drop large set of tables in SQL Server

Is there anyway to drop around 5 million table using single a SQL query or any other fast way?

  1. View object explorer is not responding

  2. select top 1000 'drop table '+ name +';' from sys.tables where name like 'xxxx%' takes 2 minutes

  3. Looping and drop table also takes around 2 minutes

Upvotes: 4

Views: 825

Answers (2)

Mark Adelsberger
Mark Adelsberger

Reputation: 45659

So the two approaches you've tested, which are quite different, each take 2 minutes to process 1000 tables. This strongly suggests that the time is being consumed by the actual act of dropping the tables, not by the method of specifying what to drop. In other words, there probably is no clever way to do it faster, if you can't migrate to a new database and drop this one.

In fairness, that's less than 1/8sec per drop. In computer time, 1/8sec is a long time I guess; but a fair amount has to be done to drop a table while maintaining the integrity of the DBMS, and presumably while other things are going on. So it doesn't seem outrageous that this might simply be the pace at which the work can be done.

5,000,000 tables at 500 tables per minute is just under 1 week. IMO you might as well let that process get started, and now you know how long you have to think of something better :)

Upvotes: 3

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

Without looping (SQL Server 2017+):

DECLARE @sql NVARCHAR(MAX) = (select 'DROP TABLE ' + string_agg(name,',')  
                              from sys.tables where name like 'xxxx%');
PRINT @sql;  -- debug only
EXEC(@sql);

DBFiddle Demo

Upvotes: 2

Related Questions