DROP IF EXISTS Statement in Sql Server 2016

DROP IF EXISTS Statement in Sql Server 2016

Sql server 2016 provided IF EXISTS in the existing DROP statement to check the existence of the object. If the object exists it drops it and if it doesn’t exist it will continue executing the next statement.

The main purpose of this statement to avoid writing if condition and within if condition writing a statement to check the existence of the object.

Syntax:  DROP OBJECT_TYPE [IF EXISTS] OBJECT_NAME

OBJECT_TYPE can be Table, Procedure, View, Function, Database, Trigger, Assembly, Sequence, Index etc.

Example 1: DROP Stored Procedure IF EXISTS

In previous version of SQL server, if we want to drop the stored procedure if exists we use statement like below.

/********** Previous version of SQL SERVER *******/

IF EXISTS (SELECT 1 FROM sys.procedures

          WHERE Name = 'SP_GET_EMPLOYEE')

BEGIN

    DROP PROCEDURE dbo.SP_GET_EMPLOYEE

END

SQL SERVER 2016 provide DROP IF EXISTS feature to reduce the complexity of the developer and reduce to writing the number of code.

Here is the simple code to drop the stored procedure if exist.

/********** SQL SERVER 2016 *******/

DROP PROCEDURE IF EXISTS dbo.SP_GET_EMPLOYEE

Example 2: DROP TABLE IF EXISTS

In previous version of SQL server, if we want to drop the table if exists we use statement like below.

/********** Previous version of SQL SERVER *******/

IF EXISTS(SELECT 1 FROM sys.Tables

          WHERE  Name = N'Employee' AND Type = N'U')

BEGIN

  DROP TABLE dbo.Employee

END

END

SQL SERVER 2016 provide DROP IF EXISTS feature to reduce the complexity of the developer and reduce to writing the number of code.

Here is the simple code to drop the table  if exist.

/********** SQL SERVER 2016 *******/

DROP PROCEDURE IF EXISTS dbo.SP_GET_EMPLOYEE

Example 3: DROP Database IF EXISTS

In previous version of SQL server, if we want to drop the Database if exists we use statement like below.

/********** Previous version of SQL SERVER *******/

IF DB_ID ('SqlManager') IS NOT NULL

BEGIN

    DROP DATABASE SqlManager

END

SQL SERVER 2016 provide DROP IF EXISTS feature to reduce the complexity of the developer and reduce to writing the number of code.

Here is the simple code to drop the Database if exist.

/********** SQL SERVER 2016 *******/

DROP PROCEDURE IF EXISTS dbo.SP_GET_EMPLOYEE