DROP IF EXISTS Table Or Other Objects In SQL Server

Checking whether a database objects like table, stored procedure, function, etc. exists and then dropping them is always the proper way to drop an object from the database. Till SQL Server 2014 there were no straightforward way to check whether the object exists before executing the drop script. You might have used some workaround like using an IF condition to check whether the object exists in SYS.OBJECTS or INFORMATION_SCHEMA and in side the IF condition writing the drop script.

Finally, in SQL Server 2016, Microsoft introduced an IF EXISTS optional class to the DROP statement. When adding IF EXISTS to the drop statement, it will drop the object only when it exists in the database, else it will continue executing the next statement without throwing any error. The syntax of using DROP IF EXISTS (DIY) is:

/* Syntax */
DROP object_type [ IF EXISTS ] object_name

As of now, DROP IF EXISTS can be used in the objects like database, table, procedure, view, function, index, trigger, default, rule, schema, aggregate, assembly, role, type, user, security policy, sequence and synonym. IF EXISTS option can also be used in ALTER TABLE statement to drop column or constraint.

If you try dropping a non-existing object without using IF EXISTS, you will get an error. This is not good when you are running a script file with lot of statements.

DROP TABLE SomeTable
GO
 
/* Error Message */
Msg 3701, Level 11, State 5, Line 471
Cannot drop the table 'SomeTable', 
because it does not exist or you do not have permission.

When you use IF EXISTS option in DROP statement, even if the object is not available, the SQL Server engine will continue executing the next statement without throwing any error. If you notice the below example even when the table is not available, there is no error at all and continues executing the next statement.

DROP TABLE IF EXISTS SomeTable
Select GetDate()
GO
 
/* Result */
-----------------------
2017-10-28 22:00:29.867
 
(1 row(s) affected)

Examples Of Using DROP IF EXISTS

As I have mentioned earlier, IF EXISTS in DROP statement can be used for several objects. In this article, I will provide examples of dropping objects like database, table, procedure, view and function, along with dropping columns and constraints. Lets start with creating a database and these objects.

/* Create a database */
CREATE DATABASE MyTecBitsSampleDB
GO
 
USE MyTecBitsSampleDB
GO
 
/* Create a table */
CREATE TABLE MyTecBitsUsers  (UserId INT, UserName Varchar(100))
GO
 
/* Create a constraint */
ALTER TABLE MyTecBitsUsers
ADD CONSTRAINT unUserID UNIQUE (UserId);   
GO
 
/* Create a stored procedure */
CREATE PROCEDURE CurrentDate
AS
BEGIN
    SELECT GetDate()
END
GO
 
/* Create a view */
CREATE VIEW vwMyTecBitsUsers
AS
    Select * from MyTecBitsUsers
GO
 
/* Create a user defined function */
CREATE FUNCTION dbo.fnSayHello(@Name Varchar(50))
RETURNS varchar(100)
AS
BEGIN
    RETURN('Hello ' + @Name + '!')
END

DROP Stored Procedure IF EXISTS

In SQL Server 2016 and later versions, you can use the below statement to drop a stored procedure only if it exists.

DROP PROCEDURE IF EXISTS CurrentDate
GO

In SQL Server 2014 and older versions, you can use the below statement with IF condition to achieve the same result:

IF  EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'CurrentDate') 
          AND type in (N'P', N'PC'))
    DROP PROCEDURE CurrentDate
GO

You may be interested in finding a stored procedure if it exists in the database.

DROP View IF EXISTS

In SQL Server 2016 and higher versions, use this below statement to drop a view.

DROP VIEW IF EXISTS vwMyTecBitsUsers
GO

For SQL Server 2014 and older versions, use this statement to  drop the view if it exists.

IF  EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'vwMyTecBitsUsers') 
             AND type = N'V')
    DROP VIEW vwMyTecBitsUsers
GO

DROP Function IF EXISTS

To drop a function if it exists in SQL Server 2016 and higher:

DROP FUNCTION IF EXISTS fnSayHello
GO

To drop a function if in SQL Server 2014 and older:

IF  EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'fnSayHello') 
          AND type = N'FN')
    DROP FUNCTION fnSayHello
GO

You may be interested in the article for finding the user defined function if it exists in a database.

DROP Constraint IF EXISTS

To drop a constraint from a table without any error even if the constraint is not available in the table:

ALTER TABLE MyTecBitsUsers DROP CONSTRAINT IF EXISTS unUserID
GO

In SQL Server 2014 and older, you can use the below method to do the same:

IF  EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'unUserID') 
             AND type = N'UQ')
    ALTER TABLE MyTecBitsUsers DROP CONSTRAINT unUserID
GO

DROP Column IF EXISTS

Follow the below script for dropping an column in a table on SQL Server 2016 and higher.

ALTER TABLE MyTecBitsUsers DROP COLUMN IF EXISTS UserName
GO

To drop a column without error on SQL Serve 2014 and older.

IF  EXISTS (SELECT 1 FROM sys.columns Where object_id = OBJECT_ID(N'MyTecBitsUsers') 
             AND name = 'UserName')
    ALTER TABLE MyTecBitsUsers DROP COLUMN UserName
GO

You may be interested in reading this article to find a column if it exists in a database.

DROP Table IF EXISTS

Script to drop table if it exists in the database:

DROP TABLE IF EXISTS MyTecBitsUsers
GO

On older versions of SQL Server, follow the below method.

IF  EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'MyTecBitsUsers') 
               AND type = N'U')
    DROP TABLE MyTecBitsUsers
GO

Read this article to find a table if it exists or not.

DROP Database IF EXISTS

Finally, to drop a database from SQL server 2016 and higher without error even if it is not available.

DROP DATABASE IF EXISTS MyTecBitsSampleDB
GO

Script to drop database only when it is available on older versions of SQL Server.

IF  EXISTS (SELECT 1 FROM sys.databases WHERE database_id = DB_ID(N'MyTecBitsSampleDB'))
    DROP DATABASE MyTecBitsSampleDB
GO

You can use similar drop if exists syntax to drop other objects mentioned earlier to avoid unwanted errors.

Reference

• About SQL Servers DROP IF EXISTS functionality introduced in SQL Server 2016.