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..
Check if User table or Temp table exists in database
