DROP IF EXISTS

SQL Server 2016 has released an optional IF EXISTS clause with DROP statement that can be used as DROP IF EXISTS.

DROP IF EXITS verify if an object exists then drops an existing object and recreate an object else continues executing specified T-SQL code.



Sometimes, you need to check if an object exists then drop and recreate an object again, in this case  DROPT IF EXISTS statement can be useful as it reduce an effort of writing T-SQL code that uses system catalog view or any Metadata function to checking if an object exists then drop and recreate an object.

SYNTAX

DROP OBJECT_TYPE [ IF EXISTS ] OBJECT_NAME

OBJECT_TYPE  can be a Database, Table, View, Schema, Procedure, Function, Synonyms, constraint and so on..

IF EXISTS  is an optional, if it is specified then it check if an object exists then drops an existing object and recreate an object else continues executing T-SQL.

OBJECT_NAME is a name of an object.

Lets take a look at DROP IF EXISTS in SQL Server.

First we will see why do we have a need of using DROP command while creating any object.

Using DROP IF EXISTS on Table

Following T-SQL demonstrate, before the releasing of IF EXISTS clause with DROP statement, how it is checked that if any table exists in database or not, if exist then drop and recreate a table.

IF EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'dbo.SampleTable') AND type in (N'U'))
DROP TABLE dbo.SampleTable;
GO
CREATE TABLE dbo.SampleTable
(
Id INT,
Name VARCHAR(50)
);
GO

Or 

IF OBJECT_ID('dbo.SampleTable', 'U') IS NOT NULL
DROP TABLE dbo.SampleTable;
GO
CREATE TABLE dbo.SampleTable
(
Id INT,
Name VARCHAR(50)
);
GO

Lets see, how the same T-SQL can be written using DROP IF EXISTS.



You can see, DROP IF EXISTS replaces the code that used SQL Server Metadata function OBJECT_ID to check the existence of an table.

DROP TABLE IF EXISTS dbo.SampleTable;
GO

CREATE TABLE dbo.SampleTable
(
Id INT,
Name VARCHAR(50)
);
GO

[Also Read: How to check if User table or Temp table exists in database?]

In case if you want to check that table exists then drop only and do not want to recreate it then you can simply execute the below statement, it will drop a table if exits. In case if table does not exist it will not return any error.

DROP TABLE IF EXISTS dbo.SampleTable; 

GO

Using DROP IF EXISTS On Stored Procedure

Following T-SQL demonstrate before the releasing of IF EXISTS clause with DROP statement, how it checked that if any stored procedure exists or not, if exist then drop and recreate.



Lets create a sample stored procedure that fetch data from SampleTable table based on value provided to @Id parameter.

IF OBJECT_ID('Usp_GetData', 'P') IS NOT NULL 
DROP PROCEDURE Usp_GetData;
GO

CREATE PROCEDURE Usp_GetData 
@ID AS INT
AS BEGIN 
SELECT * FROM dbo.SampleTable 
WHERE Id =@Id;
END
GO

Lets drop and recreate a stored procedure using DROP IF EXITS clause.

You can see, no need of using a use code to check whether a stored procedure exist or not in database or not, that is simply done by a DROP IF EXISTS clause.

DROP PROCEDURE IF EXISTS Usp_GetData;
GO

CREATE PROCEDURE Usp_GetData 
@ID AS INT
AS BEGIN 
SELECT * FROM dbo.SampleTable 
WHERE Id =@Id;
END
GO

In case if you want to check only for stored procedure existence and drop if exists and do not want to recreate it then just execute the below statement only.

DROP PROCEDURE IF EXISTS Usp_GetData;

GO

In case, if stored procedure does not exist, this statement will not give any error.

Drop a table column Using DROP IF EXISTS

Suppose you want to delete a column from using a condition if column exists then delete.



First we will see, how it is done without using DROP IF EXISTS.

Lets check, if the Name column does exist in table SampleTable, and if it exists then delete the column.

IF EXISTS (SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = N'dbo.SampleTable'
AND COLUMN_NAME = N'Name')

ALTER TABLE dbo.SampleTable DROP COLUMN [NAME]

As you can see, to check for the column existence in a table, system information schema views for column is used, and used an Alter table command to drop the column, if it exists.

Lets see, how it can be done in a simple way with less T-SQL code using a DROP IF EXISTS.

First we will quickly add a Name column again in SampleTable table as above statement has been deleted the column from table as shown below.

ALTER TABLE dbo.SampleTable ADD NAME VARCHAR(50)

Now you can see, the name column is added to table as shown below.

SELECT * FROM dbo.SampleTable

Lets use DROP IF EXISTS to check for column existence and drop if exist.

ALTER TABLE dbo.SampleTable DROP COLUMN IF EXISTS [NAME]

You can see, How DROP IF EXISTS clause make it easy and reduce an effort of writing a condition to check for column existence.

Lets see whether the column is deleted or not from table.

SELECT * FROM dbo.SampleTable

As you can see, now there is no Name column in table.

In similar way, you can use DROP IF EXISTS with View, Triggers, Schema, Synonyms, Constraints and so on.. as per your requirement.

Also Read..

SQL Create table

SQL Stored procedure

Check if User table or Temp table exists in database 

SQL DROP statement

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 431 total views,  3 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.