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

25th Feb 2013

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.