Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

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

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

3 Answers

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.

share|improve this answer
add comment

Try this.

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

Check this

Link

share|improve this answer
    
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
add comment
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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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