0

When I've changed my schema in ways that EF migrate doesn't work (it tries, it really really tries), I want to delete all the tables from my databases. Is there a way to do so other than a SQL script that first deletes the content of all the tables, then drops all the tables, in the order required by the PK:FK relationships?

None of the answers here worked.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
David Thielen
  • 28,723
  • 34
  • 119
  • 193
  • How about scripting the database without the tables (and any other objects) and then drop and create the database with the script? – Mark Kram Jul 24 '23 at 15:41
  • 3
    I'd just run this `SELECT 'DROP TABLE IF EXISTS ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';' FROM sys.tables` and then paste the results into SSMS and run it as many times as needed until none remain. Errors due to not being able to drop due to FK aren't batch aborting so it can just carry on after those errors. - if you use schema binding in views etc you may need to remove this first – Martin Smith Jul 24 '23 at 15:44
  • What happens when you just do a drop table xyz? Error, blue screen, computer shutdown – siggemannen Jul 24 '23 at 15:44
  • 1
    Would be nice if you could just pass the entire list to `DROP TABLE IF EXISTS T1, T2, T3` and it would not raise a "Could not drop object ... because it is referenced by a FOREIGN KEY constraint." error if the list was in the "wrong" order but we aren't that lucky – Martin Smith Jul 24 '23 at 15:54
  • 2
    If the only blocker is foreign keys (and not indexed views or other dependencies), you could always just script out the drop of the foreign keys ([ideas here](https://stackoverflow.com/a/11639982/61305)), then you can just generate a single drop command with all the tables as Martin suggested. – Aaron Bertrand Jul 24 '23 at 16:07

1 Answers1

0

Is there a way to do so other than a SQL script that first deletes the content of all the tables, then drops all the tables, in the order required by the PK:FK relationships?

There's no need to delete the rows. That's an expensive operation. Just drop the tables.

And you can either script a DROP for all the foreign keys, or just run the table drop script over and over until all the tables are gone (assuming you have no circular FK dependencies).

But it will be easier to just rename the old database and create a new one.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • It's an Azure SQL Database so deleting the old and creating a new one is a royal PITA because it's several settings and then wait as it is created. But yeah, probably the cleanest solution. Thank you – David Thielen Jul 24 '23 at 19:44
  • BTW, if by any chance you're on the SQL Database team at Microsoft - having a **Clear** command would be great. That's one step short of **Delete** where it wipes all the data and tables, but the database still exists. – David Thielen Jul 24 '23 at 19:46
  • I'm not on that team, but you can share feedback for it here: https://feedback.azure.com/d365community/forum/ef2b2b38-2f25-ec11-b6e6-000d3a4f0f84 – David Browne - Microsoft Jul 24 '23 at 20:54