🌤

Drop all tables and reset an Azure SQL Database

azuresql
Loading...
 

Published on

February 24, 2022

During the development sometimes you need to reset and recreate the database from scratch. Especially if you work with Entity Framework and you alter a lot of tables and keys. For this reason I created this SQL Script that you can launch directly as query from the Azure Portal or from Visual Studio 2022 or Visual Studio Code.

As you can see in the screenshot below, I am connected trought VS 2022 SQL Server Object Explorer.

Image description

The script works well with Entity Framework 6. If you want to use the same script with previous version of EF, check the name of the MigrationHistory. It was different in some versions.

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

while(exists(select 1 from INFORMATION_SCHEMA.TABLES 
             where TABLE_NAME != '__EFMigrationsHistory' 
             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 != '__EFMigrationsHistory' AND TABLE_TYPE = 'BASE TABLE'
exec (@sql)
 /* you dont need this line, it just shows what was executed */
 PRINT @sql
end

exec ('DROP TABLE dbo.__EFMigrationsHistory')

React, comment and follow on