What is the most efficient way to drop 200+ tables except for a few tables in a given DB?

I need to remove 200+ tables for a copy of DB and only keep a few that is needed for analysis. All of them are columnstore tables with shard key defined.

Would running TRUNCATE TABLE before DROP TABLE help with the performance?

No. Both TRUNCATE TABLE and DROP TABLE are non-logged and run in a transaction by themselves. So both are going to be pretty fast. Doing truncate first will be extra work.

1 Like