4

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

Improve this question
19
  • I would probably move rest of tables to another schema and drop that schema (if not dbo). Then rename new one to old name. Commented Apr 18, 2018 at 14:57
  • Thanks. Not possible as this is in a production environment. Commented Apr 18, 2018 at 14:59
  • Did you not provide a way to do this already? What's the real question--how to do it faster, or...? Commented Apr 18, 2018 at 15:01
  • 6
    5 million tables??? That sounds like the worst data model ever. I cannot imagine a database dealing with 5 million different entities. Are you in the process of fixing that? One or few tables with millions of rows instead of millions of tables? Commented Apr 18, 2018 at 15:17
  • 1
    Why does it even matter if it was just by mistake and you're just clearing it? Are you planning to make the same mistake over and over again... :) Commented Apr 18, 2018 at 15:33

2 Answers 2

Reset to default
3

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 :)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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

Improve this answer

1 Comment

Thanks. Expecting something to do with using sys.objects or sys.tables

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.