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.