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.
- About SQL Servers DROP IF EXISTS functionality introduced in SQL Server 2016.