Azure, Entity Framework, SQL Server

Drop all tables in a SQL Server database (Azure Friendly!)

Most online solutions to this problem will point you in the direction of the undocumented stored procedure, “sp_MSforeachtable“. This stored procedure takes in a string of SQL as a parameter, and will execute this string for each non system table in your database.

Here’s the problem: undocumented stored procedures suck. Their usage has been determined by reverse engineering. They are not officially supported. Their implementation and usage could change with updates and new versions, or could disappear totally.

Here’s something that illustrates my point: “sp_MSforeachtable” does not exist in SQL Azure. So, if your development environment is SQL Server 2008 but your production environment is SQL azure and you are using “sp_MSforeachtable“, you will get problems when you go live, which sucks.

Below is a simple, Azure and Entity Framework friendly bit of SQL that will drop all tables in your database, with the exception of your entity framework migration history table – “__MigrationHistory“:


while(exists(select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME != '__MigrationHistory'))
begin
 declare @sql nvarchar(2000)
 SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
 + ']')
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_NAME != '__MigrationHistory'
exec (@sql)
 PRINT @sql
end

 

If you need to drop your table constraints first, the following code will allow you to do so:


while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
begin
 declare @sql nvarchar(2000)
 SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
 + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
 FROM information_schema.table_constraints
 WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
 exec (@sql)
 PRINT @sql
end

Credit to SQL Server Central for the above snippet.

18 thoughts on “Drop all tables in a SQL Server database (Azure Friendly!)

  1. Thanks for your post

    I was using sp_MSforeachtable because it could preserve your constraints after you removed them by doing:
    EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
    EXEC sp_MSForEachTable ‘DELETE FROM ?’
    EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’

    I don’t know a way to put them back with the above script. However there is a gist available of the stored proc that just works in SQL Azure. https://gist.github.com/metaskills/893599

    Kr,
    Jonas

  2. Thanks Ed! Took out the __MigrationHistory checks for my situation (drop the whole enchilada) and it worked perfectly. I changed the @sql to @sqlTables in the drop code so I could run the drop constraints and drop tables in the query (it was complaining about declaring @sql twice). Appreciate it.

  3. It worked great for me. Just missing a way to drop user-generated stored procedures. Can anyone help? Thanks everybody!

  4. Hi,

    I tried the delete all tables but it failed because there is a table called ‘database_firewall_rules’ which you cannot delete and you code gets stuck in a loop. I changed it to:

    while(exists(select 1 from INFORMATION_SCHEMA.TABLES where
    TABLE_NAME != ‘__MigrationHistory’ AND TABLE_TYPE = ‘BASE TABLE’))
    begin
    declare @sql nvarchar(2000)
    SELECT TOP 1 @sql=(‘DROP TABLE ‘ + TABLE_SCHEMA + ‘.[‘ + TABLE_NAME
    + ‘]’)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME != ‘__MigrationHistory’ AND TABLE_TYPE = ‘BASE TABLE’
    exec (@sql)
    PRINT @sql
    end

    Hope that helps.

    Jon Smith – http://www.thereformedprogrammer.net

  5. Help, Getting this error
    Msg 3701, Level 11, State 5, Line 1
    Cannot drop the table ‘sys.database_firewall_rules’, because it does not exist or you do not have permission.

Leave a Reply to lmpctect Cancel reply

Your email address will not be published. Required fields are marked *