How To Use DROP IF EXISTS in SQL Server

SQL Server 2016 edition has included an awe-inspiring feature in Database engine that is DROP IF EXISTS along with a bunch of superior features.

Option DROP IF EXISTS is used when we need to verify if an object exists in a database before creating/ dropping it. In this situation, we need to first drop existing database object and recreate with any modifications.

Prior to SQL Server 2016 using DROP IF EXISTS option included writing lengthy IF statement wrappers code. With the introduction of DROP IF EXISTS, developers can write more concise code. Listed new features in SQL Server 2016 on msdn.

This Tech-Recipes tutorial explains how to use DROP IF EXISTS in SQL SERVER with helpful examples.

Prior SQL Server 2016 – Using DROP IF EXISTS on Database Objects

IF OBJECT_ID('dbo.Test', 'U') IS NOT NULL
DROP TABLE dbo.Test;
GO

CREATE TABLE dbo.Test
(
    Id INT
);
GO

IF OBJECT_ID('dbo.Insert_Test', 'P') IS NOT NULL
DROP PROCEDURE dbo.Insert_Test;
GO

CREATE PROCEDURE dbo.Insert_Test
AS
BEGIN
    SELECT 1;
END
GO

SQL Server 2016 & Later – Using DROP IF EXISTS On Database Objects

Let’s walk-through with few examples of important database objects to see how we can use DROP IF EXISTS option effectively.

1. Creating & Dropping Table using DROP TABLE IF EXISTS

In the following example, the first statement will check if a table named Test exists in the tempdb database. If it does exists then it will try to DROP the table.

Although if Test table doesn’t exist, DROP TABLE IF EXISTS doesn’t cause any error it silently ignores it.

The second statement will create a Test table.

Use tempdb
GO

DROP TABLE IF EXISTS dbo.Test;
GO

CREATE TABLE dbo.Test
(
    Id INT
)
GO

Using it on Temp Tables in SQL Server.

Use tempdb
GO

DROP TABLE IF EXISTS dbo.#Test_Temp;
GO

CREATE TABLE dbo.#Test_Temp
(
    Id INT
)
GO

2. Creating & Dropping Stored Procedure using DROP PROCEDURE IF EXISTS

In the following example, the first statement will check if a stored procedure named Insert_Test exists in the tempdb database. If it does exists then it will try to DROP the stored procedure.

Although if Insert_Test stored procedure doesn’t exist, DROP PROCEDURE IF EXISTS doesn’t cause any error it silently ignores it.

The second statement will create a Insert_Test stored procedure.

Use tempdb
Go

DROP PROCEDURE IF EXISTS dbo.Insert_Test;
GO

CREATE PROCEDURE dbo.Insert_Test
AS
BEGIN
    SELECT 1;
END
GO

3. Creating & Dropping View using DROP VIEW IF EXISTS

In the following example, the first statement will check if a view named Vw_Test exists in the tempdb database. If it does exists then it will try to DROP the view.

Although if Vw_Test view doesn’t exist, DROP VIEW IF EXISTS doesn’t cause any error it silently ignores it.

The second statement will create a Vw_Test view.

Use tempdb
Go

DROP VIEW IF EXISTS dbo.Vw_Test;
GO

CREATE VIEW dbo.Vw_Test
AS
    SELECT 1 as Col;
GO

Similarly we can use DROP IF EXISTS on following database objects in SQL Server 2016 and beyond.

DROP IF EXISTS ASSEMBLY <Assembly_Name>;
DROP IF EXISTS ROLE <Role_Name>;
DROP IF EXISTS TRIGGER <Trigger_Name>;
DROP IF EXISTS TYPE <Type_Name>;
DROP IF EXISTS DATABASE <Data_Name>;
DROP IF EXISTS SCHEMA <Schema_Name>;
DROP IF EXISTS USER <User_Name>;
DROP IF EXISTS DEFAULT <Default_Name>;
DROP IF EXISTS SECURITY POLICY <Policy_Name>;
DROP IF EXISTS VIEW <View_Name>;
DROP IF EXISTS FUNCTION <Function_Name>;
DROP IF EXISTS SEQUENCE <Sequence_Name>;
DROP IF EXISTS INDEX <Index_Name>;
DROP IF EXISTS SYNONYM <Synonym_Name>;

Read more about Database and SQL programming from Tech-Recipes.