SQL: Reliably Dropping a Database in a T-SQL Script is Too Hard

SQL: Reliably Dropping a Database in a T-SQL Script is Too Hard

I often need to write scripts that drop and recreate databases. The hard part of that has always been reliably dropping a database if it already exists. And no, you wouldn’t think that would be hard, but it is.

Built in Command

T-SQL has a built-in command for this.

DROP DATABASE IF EXISTS Sales;

You’d hope that would work, but it doesn’t.  I wish it did. The problem is that it will fail if anyone is connected to the DB. And to check if anyone is attached, you first need to check if the DB exists, so it makes the whole IF EXISTS part that was added to this command, completely pointless.

Worse, if you have separate code to kick everyone off first, you always have a chance of a race condition, between when you kick everyone off, and when you execute the command.

Nearly OK

Years back, the Microsoft docs library said to drop a database basically like this:

USE master;
GO
IF EXISTS (SELECT 1 FROM sys.databases WHERE [name] = N'Sales')
BEGIN
    ALTER DATABASE Sales SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE Sales;
END;
GO

This was promising, but unfortunately, it has an issue as well. Because you were in the master database when you issued the ALTER, you don’t know that you are the single user.

And this happens more often if you are executing the script from within SQL Server Management Studio (SSMS). What I’ve found is that the Intellisense system spends its time scanning your script, including the code that comes after the drop and recreate, and it ends up holding a shared lock on the current database.

So, periodically, that method would fail too. I’ve shipped out scripts to learners, and they’ve run the script many times, and out of perhaps a 100 executions, it might fail 2 or 3 times. That’s just not acceptable.

Best Workaround

We had a discussion on an MVP list about how to work around this. Many thanks to Paul White, Erland Sommarskog, and Simon Sabin for contributing to ideas on how to work around it.

The best outcome I have right now is to use this:

USE tempdb;
GO

DECLARE @SQL nvarchar(max);

IF EXISTS (SELECT 1 FROM sys.databases WHERE [name] = N'Sales') 
BEGIN
    SET @SQL = 
        N'USE Sales;
          ALTER DATABASE Sales SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
          USE master;
          DROP DATABASE Sales;';
    EXEC (@SQL);
    USE tempdb;
END;
GO

To get the DROP to work properly, you need to execute the ALTER DATABASE from within the target database. That way, you end up being the single user, and even though you then execute a change to master, you hold the required session lock on the DB, and then the drop works as expected.

Because you can’t have a USE Sales in the script if the Sales DB doesn’t exist, this unfortunately has to be done in dynamic SQL code, where it is only executed if the DB does exit.

The last change to tempdb is just protection, if I have a script that then wants to create the DB and change to using it. If that goes wrong, I want to end up creating things in tempdb, not somewhere else like master.

I’ve now set this up as a code snippet that I can use from within SSMS.

What I wanted

What I’ve been asking for, and for a very long time, is this:

DROP DATABASE IF EXISTS Sales WITH ROLLBACK IMMEDIATE;

The ROLLBACK IMMEDIATE needs to be on the DROP DATABASE command, not on a separate ALTER command. Hopefully one day we’ll get this.

2026-05-28