How To Use DROP IF EXISTS in SQL Server?

This article discusses the “DROP IF EXISTS” statement available in SQL Server 2016 and later versions.  “IF EXITS” is the latest optional clause added in the existing DROP statement in SQL Server 2016 and later versions. Essentially, the “DROP IF EXISTS” option is used when it is necessary to check whether an entity remains in a database before it is created or dropped. In this case, we will first drop the existing database object and then recreate it with changes if required.

Thus, it prevents the old way of writing if condition and inside the if condition writing a statement to test the object’s presence to drop it. If it does not occur, the next statement in the batch will continue to be executed. But if we try to drop an object that does not exist then it will raise an error message as shown below.

Execute the following query.

drop table dbo.company

The output will be like this.

Syntax

“DROP object_type [ IF EXISTS ] object_name”

Arguments

OBJECT_TYPE:

The object type can be anyone from the database, trigger, assembly, sequence, index, table, procedure view, function, etc.

IF EXISTS:

It is an optional clause and if it is mentioned in the DROP statement, it will check the object’s existence, if it exists it will drop, otherwise it will continue to execute the next statement in the block without producing any errors.

Now with the introduction of the new method “DROP IF EXISTS” in SQL Server 2016 developers can write brief code.

First, create a database named “appuals”.

Now, we will create a table to be dropped by executing the following code.

use [appuals]
Go
CREATE TABLE temp
(
    id INT,
    name varchar(100)
);
GO

The output will be as follows.

Further, create a store procedure to be dropped using the following code.

USE [appuals]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[sp_temp]
AS
BEGIN
       SET NOCOUNT ON;
       SELECT * from dbo.temp;
END

The output will be as below.

Old method: Prior to SQL Server using drop if exits on database objects

Using the DROP IF EXISTS method before SQL Server 2016 required writing lengthy IF statement wrappers code.

Drop table if exists

The prior method of dropping a table is as follows.

If we are using SQL Server 2015 or earlier than we need to execute the following bunch of code.

If(OBJECT_ID('dbo.temp') Is Not Null)
Drop Table temp

The output will be as follows.

Now its syntax is quite confusing so if you don’t like it and you are using SQL Server 2016 or above you can go for simple DROP IF EXIST statement instead of big wrappers.

Drop store procedure if exists:

To drop the procedure, we have to write a conditional statement to check if the store procedure exists or not then write the drop statement. Otherwise, it will raise an error in case the stored procedure does not exist.

Now execute the following statements to drop the procedure in versions lower than SQL Server 2016.

IF EXISTS(SELECT 1 FROM sys.procedures WHERE Name = 'sp_temp')
DROP PROCEDURE dbo.sp_temp

The output will be like this.

Drop database if exists:

If you are using previous versions of SQL Server you need to execute the following code to drop the database.

IF DB_ID('appuals') IS NOT NULL
BEGIN
DROP DATABASE appuals
END

The output will be as follows.

New method: DROP IF EXISTS supported in SQL Server 2016 and above version

To drop a database object in SQL Server 2016 and above we need to execute a simple statement.

Drop table if exists:

We can write a statement as below in SQL Server 2016 to remove a stored table if it exists.

DROP TABLE IF EXISTS dbo.temp

The output will be like this.

Drop procedure if exists:

Now we will drop the stored procedure we created at the start of the article by executing the following code.

DROP PROCEDURE IF EXISTS dbo.sp_temp

The output will be as below.

As far as syntax is concerned, this simple statement is easy to understand and easy to remember. Similarly, we can follow the same procedure to drop other database objects.

Drop database if exists:

Execute the following code if you want to drop database by using if exists check

USE MASTER
GO
DROP DATABASE IF EXISTS appuals

The output will be as below.

The benefit of using this method is that if the database doesn’t exist it won’t cause any error, the next statement in the batch will continue to be executed. Let’s try to re-drop the already dropped database.

Similarly, we can drop indexes, views, sequences, assemblies, etc. from the database.