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

drop-tables.sql

-- original source: http://edspencer.me.uk/2013/02/25/drop-all-tables-in-a-sql-server-database-azure-friendly/

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

GO

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

I think that both queries must be filtered to only drop user tables and constraints. The second query will try to delete sys objects.

while(exists(select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME != '__MigrationHistory' and TABLE_SCHEMA = 'dbo'))
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_SCHEMA = 'dbo'
exec (@sql)
 PRINT @sql
end

The schema must be inside square brackets as well. In my case the schema name "Identity" could not be deleted without it.