How to drop all tables and reset an Azure SQL Database

Since there is not an API way to do this that I am aware of, we have used this script to leverage a T-SQL query to clear the database.

To delete each table (and maintain your EF migration histories if you want)

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) /* you dont need this line, it just shows what was executed */ PRINT @sqlend

To remove the foreign keys first if you need to

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 @sqlend

In my testing, this worked without issues (except I did not have the where clause in the DROP TABLE while query since I don't use Code First or EF migrations).

Just to add to the answers since the accepted answer did not work for me on Azure.Use the below script to Delete all the tables and basically reset the azure database.It firstly deletes all constraints and then drops all of the tables.

As @Skorunka František commented this script assumes you use the default [dbo] schema. Although you could replace it with your own schema name.

/* Azure friendly *//* Drop all Foreign Key constraints */DECLARE @name VARCHAR(128)DECLARE @constraint VARCHAR(254)DECLARE @SQL VARCHAR(254)SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)WHILE @name is not nullBEGIN    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)    WHILE @constraint IS NOT NULL    BEGIN        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'        EXEC (@SQL)        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)    ENDSELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)ENDGO/* Drop all Primary Key constraints */DECLARE @name VARCHAR(128)DECLARE @constraint VARCHAR(254)DECLARE @SQL VARCHAR(254)SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)WHILE @name IS NOT NULLBEGIN    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)    WHILE @constraint is not null    BEGIN        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'        EXEC (@SQL)        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)    ENDSELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)ENDGO/* Drop all tables */DECLARE @name VARCHAR(128)DECLARE @SQL VARCHAR(254)SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])WHILE @name IS NOT NULLBEGIN    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'    EXEC (@SQL)    PRINT 'Dropped Table: ' + @name    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])ENDGO

Sadly I cannot find the source for this code anymore as i had it saved in one of my repositories.I hope it helps someone.

Just to add my variant to the mix... This one also takes into account Views and External Tables. It will not barf on External Tables which have to be removed separately with DROP EXTERNAL TABLE. This caused the original version to spin forever.

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 @sqlendwhile(exists(select 1 from INFORMATION_SCHEMA.TABLES     where TABLE_NAME != 'database_firewall_rules'     AND TABLE_TYPE = 'BASE TABLE'    AND TABLE_NAME NOT IN (select name from sys.external_tables)))begin declare @sql1 nvarchar(2000) SELECT TOP 1 @sql1=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != 'database_firewall_rules'    AND TABLE_TYPE = 'BASE TABLE'    AND TABLE_NAME NOT IN (select name from sys.external_tables)exec (@sql1) PRINT @sql1end