<p>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.</p>

[{'answer_id': 7537, 'author': 'Derek Park', 'author_id': 872, 'author_profile': 'https://Stackoverflow.com/users/872', 'pm_score': 0, 'selected': False, 'text': "<p>I don't have access to SQL Server to test this, but how about:</p>\n\n<pre><code>DROP TABLE IF EXISTS table1, table2, table3 CASCADE;\n</code></pre>\n"}, {'answer_id': 13323, 'author': 'John Smithers', 'author_id': 1069, 'author_profile': 'https://Stackoverflow.com/users/1069', 'pm_score': 0, 'selected': False, 'text': "<p>I'm not sure, if Derek's approach works. You haven't mark it as best answer yet.  </p>\n\n<p>If not: with SQL Server 2005 it should be possible, I guess.<br>\nThere 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.<br>\nYou can store the list of tables in a temp-table and use a cursor to traverse it, if you want to.</p>\n"}, {'answer_id': 25719, 'author': 'Matt Sheppard', 'author_id': 797, 'author_profile': 'https://Stackoverflow.com/users/797', 'pm_score': 0, 'selected': False, 'text': '<p>I ended up using Apache\'s <a href="http://db.apache.org/ddlutils/" rel="nofollow noreferrer">ddlutils</a> 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.</p>\n\n<p>@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 <code>drop table X, Y, Z</code> seems to work only if they should be dropped in the stated order.</p>\n\n<p>See also <a href="http://msdn.microsoft.com/en-us/library/ms173790.aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/ms173790.aspx</a>, which describes the drop table syntax.</p>\n'}, {'answer_id': 25724, 'author': 'Yaakov Ellis', 'author_id': 51, 'author_profile': 'https://Stackoverflow.com/users/51', 'pm_score': 0, 'selected': False, 'text': "<p>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.</p>\n\n<ol>\n<li>Using the INFORMATION_SCHEMA system views, retrieve a list of all foreign keys related to any of these tables</li>\n<li>Drop each of these foreign keys</li>\n<li>Now you should be able to drop all of the tables, using any order that you want.</li>\n</ol>\n"}, {'answer_id': 25726, 'author': 'ila', 'author_id': 1178, 'author_profile': 'https://Stackoverflow.com/users/1178', 'pm_score': 1, 'selected': False, 'text': "<p>A diferent approach could be: first get rid of the constraints, then drop the tables in a single shot. </p>\n\n<p>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.</p>\n"}, {'answer_id': 25741, 'author': 'Jon Galloway', 'author_id': 5, 'author_profile': 'https://Stackoverflow.com/users/5', 'pm_score': 3, 'selected': True, 'text': '<p>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 (<a href="http://msdn.microsoft.com/en-us/library/ms152507.aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/ms152507.aspx</a>), 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.</p>\n'}, {'answer_id': 103183, 'author': 'Manrico Corazzi', 'author_id': 4690, 'author_profile': 'https://Stackoverflow.com/users/4690', 'pm_score': 1, 'selected': False, 'text': '<p>This requires the <strong>sp___drop___constraints</strong> script you can find at <a href="http://www.databasejournal.com/img/usr_sp_drop_constraints.sql" rel="nofollow noreferrer">Database Journal</a>:</p>\n\n<pre><code>sp_MSforeachtable @command1="print \'disabling constraints: ?\'", @command2="sp_drop_constraints @tablename=?"\nGO\nsp_MSforeachtable @command1="print \'dropping: ?\'", @command2="DROP TABLE ?"\nGO\n</code></pre>\n\n<p><strong>NOTE</strong> this - obviously - if you meant to drop ALL of the tables in your database, so be careful</p>\n'}]
['https://Stackoverflow.com/questions/7517', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/797/']