How to drop a list of SQL Server tables, ignoring constraints?

Question

I have a list of half a dozen MSSQL 2008 tables that I would like to remove at once from my database. The data has been entirely migrated to new tables. There is no reference in the new tables to the old tables.

The problem being that old tables comes with loads of inner FK constraints that have been autogenerated by a tool (aspnet_regsql actually). Hence dropping manually all constraints is a real pain.

How can I can drop the old tables ignoring all inner constraints?

Answer 1

A simple DROP TABLE dbo.MyTable will ignore all constraints (and triggers) except foreign keys (unless you drop the child/referencing table first) where you may have to drop these first.

Edit: after comment:

There is no automatic way. You'll have to iterate through sys.foreign_keys and generate some ALTER TABLE statements.

Answer 2

I suspect that you would have to do an 'alter' command on the offending tables before the drop to remove the forigen key contraints.

ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders;
DROP TABLE Orders;

Of course if you drop the child tables first, then you wont have this problem. (unless you have table A contraint to table B and table B constraint to A, then you will need to Alter one of the tables, e.g. A to remove the constraint)

e.g. this WONT work, since Orders has a contraint from Order_Lines

DROP TABLE Orders;
DROP TABLE Order_lines;

e.g. this will work

DROP TABLE Order_lines;
DROP TABLE Orders;

Answer 3

I finally found the solution based on the script provided by Jason Presley. This script automatically removes all constraints in the DB. It's easy to add a WHERE clause so that it only applies to the set of concerned tables. After that, dropping all tables is a straightforward.