Dropping a group of tables in SQL Server

Question

Is there a simple way to drop a group of interrelated tables in SQL Server? Ideally I'd like to avoid having to worry about what order they're being dropped in since I know the entire group will be gone by the end of the process.

Answer 1

I don't have access to SQL Server to test this, but how about:

DROP TABLE IF EXISTS table1, table2, table3 CASCADE;

Answer 2

I'm not sure, if Derek's approach works. You haven't mark it as best answer yet.

If not: with SQL Server 2005 it should be possible, I guess.
There they introduced exceptions (which I've not used yet). So drop the table, catch the exception, if one occurs and try the next table till they are all gone.
You can store the list of tables in a temp-table and use a cursor to traverse it, if you want to.

Answer 3

I ended up using Apache's ddlutils to perform the dropping for me, which sorted it out in my case, though a solution which worked only within sql server would be quite a bit simpler.

@Derek Park, I didn't know you could comma separate tables there, so that's handy, but it doesn't seem to work quite as expected. Nether IF EXISTS nor CASCADE are recognised by sql server it seems, and running drop table X, Y, Z seems to work only if they should be dropped in the stated order.

See also http://msdn.microsoft.com/en-us/library/ms173790.aspx, which describes the drop table syntax.

Answer 4

The thing holding you back from dropping the tables in any order are foreign key dependencies between the tables. So get rid of the FK's before you start.

1. Using the INFORMATION_SCHEMA system views, retrieve a list of all foreign keys related to any of these tables
2. Drop each of these foreign keys
3. Now you should be able to drop all of the tables, using any order that you want.

Answer 5

A diferent approach could be: first get rid of the constraints, then drop the tables in a single shot.

In other words, a DROP CONSTRAINT for every constraint, then a DROP TABLE for each table; at this point the order of execution shouldn't be an issue.

Answer 6

At the risk of sounding stupid, I don't believe SQL Server supports the delete / cascade syntax. I think you can configure a delete rule to do cascading deletes (http://msdn.microsoft.com/en-us/library/ms152507.aspx), but as far as I know the trick with SQL Server is to just to run your drop query once for each table you're dropping, then check it worked.

Answer 7

This requires the sp___drop___constraints script you can find at Database Journal:

sp_MSforeachtable @command1="print 'disabling constraints: ?'", @command2="sp_drop_constraints @tablename=?"
GO
sp_MSforeachtable @command1="print 'dropping: ?'", @command2="DROP TABLE ?"
GO

NOTE this - obviously - if you meant to drop ALL of the tables in your database, so be careful