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

Following Ed Spencer’s Blog:

First, remove constrain fist:

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

Then, drop all tables:

1:  while(exists(select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME != '__MigrationHistory'))  
2:  begin  
3:   declare @sql nvarchar(2000)  
4:   SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME  
5:   + ']')  
6:   FROM INFORMATION_SCHEMA.TABLES  
7:   WHERE TABLE_NAME != '__MigrationHistory'  
8:  exec (@sql)  
9:   PRINT @sql  
10:  end  

Note: Copy the results of these command to use in next time.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s