Make an Object DIE in SQL Server

In SQL Server, if you try to drop an object that doesn’t exist, you’ll get an error. If you don’t want to get an error, you’ll need to add some extra code to check for the existence of the object.

Prior to SQL Server 2016, you needed to add an IF statement that queried the relevant system objects to find out whether or not the object existed.

From SQL Server 2016, you can now use a new, cleaner method to check if an object exists. We’ll call it DROP IF EXISTS (otherwise known as “DIE”).

Example 1 – Basic Syntax

The syntax doesn’t actually contain the DROP IF EXISTS string as it is. What you need to do is insert the object type between DROP and IF.

DROP TABLE IF EXISTS Customers

In this case, I’m dropping a table, so the word TABLE is inserted between DROP and IF. The name of the table I want to drop (in this case Customers) is placed at the end.

Example 2 – Basic Demo

Here’s an example of creating a database, then dropping it, then attempting to drop it again.

CREATE DATABASE Homer;
GO
DROP DATABASE IF EXISTS Homer;
GO
DROP DATABASE IF EXISTS Homer;
GO

Result:

Started executing query at Line 17
Commands completed successfully.

Started executing query at Line 19
Commands completed successfully.

Started executing query at Line 21
Commands completed successfully.

No errors occur, even though I try to drop a non-existent object on the third line.

Example 3 – Without IF EXISTS

Here it is again, except this time I remove the IF EXISTS part.

CREATE DATABASE Homer;
GO
DROP DATABASE Homer;
GO
DROP DATABASE Homer;
GO

Result:

Started executing query at Line 17
Commands completed successfully.

Started executing query at Line 19
Commands completed successfully.

Started executing query at Line 21
Msg 3701, Level 11, State 1, Line 5
Cannot drop the database 'Homer', because it does not exist or you do not have permission.

In this case I get an error, because it’s trying to drop an object that doesn’t exist.

Example 4 – Columns and Constraints

You can also use DIE on columns and constraints.

For example, you can use DROP COLUMN IF EXISTS within your ALTER TABLE statement.

Here’s an example.

DROP TABLE IF EXISTS DieTest;
GO

CREATE TABLE DieTest
(
    DieTestId uniqueidentifier NOT NULL DEFAULT NEWID(),
    DieTestName varchar(70), 
    InsertDate datetime2(7) NOT NULL DEFAULT GETDATE()
);
GO

ALTER TABLE DieTest
DROP COLUMN IF EXISTS DieTestName;
GO

ALTER TABLE DieTest
DROP COLUMN IF EXISTS DieTestName;
GO

Result:

Started executing query at Line 1
Commands completed successfully.

Started executing query at Line 3
Commands completed successfully.

Started executing query at Line 11
Commands completed successfully.

Started executing query at Line 15
Commands completed successfully.

Example 5 – Prior to SQL Server 2016

Prior to SQL Server 2016, to test for an object’s existence, you’d need to do something like this:

IF DB_ID('Homer') IS NOT NULL
DROP DATABASE Homer;

That example uses DB_ID() because we’re dealing with a database. If the object was a different type, you might need to use the OBJECT_ID() function or something else altogether.

For example:

IF OBJECT_ID('dbo.Customers, 'U') IS NOT NULL
DROP TABLE dbo.Customers;
 
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'MyTrigger')
DROP TRIGGER MyTrigger

Once we start looking at this code, we can see why DROP IF EXISTS is a welcome addition to the T-SQL syntax.

Eligible Objects

DIE can be used on the following objects.

AGGREGATE	PROCEDURE	TABLE
ASSEMBLY	ROLE	TRIGGER
VIEW	RULE	TYPE
DATABASE	SCHEMA	USER
DEFAULT	SECURITY POLICY	VIEW
FUNCTION	SEQUENCE
INDEX	SYNONYM

As mentioned, DIE can be also used on columns and constraints when using the ALTER TABLE statement:

ALTER TABLE DROP COLUMN IF EXISTS
ALTER TABLE DROP CONSTRAINT IF EXISTS