How to DROP multiple tables from databases?

Question

I have situation where someone mistakenly execute script for creating tables under wrong database.

Let's say we have database DB1 which is some test database, and we have to create other database DB2. Script for creating tables is intended for DB2, but someone execute it under DB1.

Now my database DB1 has its own correct tables, and other incorrect tables. I know how to write a select query to view them, and I know that all wrongly created tables were created on a specific date, let's say 2009-12-12. But the tables are related with foreign keys, and I can't just drop them in the order that they appear, I must first to remove all foreign keys and then drop all of the tables.

Can someone suggest how to do that?

I can do it with cursor, but that is not elegant solution.

Answer 1

Since you only have to do it once, forget the fancy batch job and just get on with each task. Assuming you know the table names, you just do this sort of thing.

For each table with foreign keys, drop the foreign key constraint on that field. Then depending on your requirement, you can add records to the table that should have the data and add a new foreign key, or, simply delete those records. Then you can drop the table.

Much of this work can be done with SSMS so you might not have to write that many queries.

Answer 2

Try this.

EXEC sys.sp_MSforeachtable 
N'IF OBJECT_ID(''?'') <> OBJECT_ID(''dbo.YourTableToKeep'')
DROP TABLE ?

Check this

Link

Answer 3

DELETE t1,t2 
FROM table1 AS t1 
INNER JOIN table2 t2 ...
INNER JOIN table3 t3 ...

like this way youu can delete multiple tables

see this enter link description here link