DEV Community

Cover image for Drop all tables and reset an Azure SQL Database
Emanuele Bartolesi
Emanuele Bartolesi

Posted on

7 2

Drop all tables and reset an Azure SQL Database

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')


Enter fullscreen mode Exit fullscreen mode

Image of Datadog

The Essential Toolkit for Front-end Developers

Take a user-centric approach to front-end monitoring that evolves alongside increasingly complex frameworks and single-page applications.

Get The Kit

Top comments (0)

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay