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.

3 Answers 3


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.


Try this.

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

Check this


  • I want one batch to delete all tables, your solution can't work if tables is referenced by another tables
    – veljasije
    Sep 24, 2013 at 12:03
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

  • The DELETE statement is used to delete rows in a table, not the tables itself.
    – veljasije
    Sep 24, 2013 at 12:09
  • i update my answer see above
    – user2801653
    Sep 24, 2013 at 12:15
  • The syntax is not valid. Plus, it does not address the foreign key problem.
    – Dan Bracuk
    Sep 24, 2013 at 12:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.