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 before dropping and recreating it. In such cases, the DROP IF EXISTS statement is useful, as it simplifies the process by eliminating the need to write T-SQL code that queries system catalog views or metadata functions to check for the object’s existence before dropping and recreating it
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.
Let’s explore the DROP IF EXISTS feature in SQL Server.
First, let’s understand why we need to use the DROP command when creating an object.
Using DROP IF EXISTS on Table
Following T-SQL demonstrate, before the releasing of IF EXISTS clause with DROP statement, how it was checked that if any table exists in database or not, if it exists 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
Now, let’s see how the same T-SQL can be written using the DROP IF EXISTS statement.
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?]
If you only want to check if a table exists and drop it without recreating it, you can simply execute the statement below.
It will drop the table if it exists, and if the table does not exist, no error will be returned
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 was checked that if any stored procedure exists or not, if it exists 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
Let’s drop and recreate a stored procedure using the DROP IF EXISTS clause.
As you can see, there’s no need to write additional code to check if the stored procedure exists in the database, this is handled directly by the 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
If you only want to check for the existence of a stored procedure and drop it without recreating it, simply execute the statement below.
DROP PROCEDURE IF EXISTS Usp_GetData; GO
If stored procedure does not exist then this statement will not give any error.
Drop a table column Using DROP IF EXISTS
Assume, you want to delete a column based on condition if column exists then delete.
First, let’s see how it is done without using the DROP IF EXISTS statement.
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 column Name 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 the DROP IF EXISTS clause simplifies the process and reduces the effort required to write a condition for checking 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
