Delete a large set of tables in SQL Server

Question

In any case, can you omit about 5 million tables using a single SQL query or any other fast way?

• View explorer object not responding

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

• The loop and drop table also takes about 2 minutes.

Answer 1

So, the two approaches that you tested, which are completely different, take 2 minutes to process 1000 tables. This suggests that time is consumed by the actual act of deleting tables, and not by the method of indicating what needs to be deleted. In other words, there probably isn’t a smart way to do this faster if you cannot migrate to the new database and abandon it.

, 1/8 . , 1/8sec - , ; , , , , , . , , .

5 000 000 500 1 . , , , - :)

Answer 2

(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

Answer 3

. , , 5000 - 10000 SSMS SSMS. 125 .