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.

migrated from stackoverflow.com Oct 6 '13 at 16:01

This question came from our site for professional and enthusiast programmers.


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 '13 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 '13 at 12:09
  • i update my answer see above – user2801653 Sep 24 '13 at 12:15
  • The syntax is not valid. Plus, it does not address the foreign key problem. – Dan Bracuk Sep 24 '13 at 12:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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