DROP IF EXISTS In SQL Server 2016

Introduction

With this very small but handy tip, we’ll check the enhancement that has been made to the DROP statement in SQL Server 2016. Earlier, we used to write additional checking logic to make sure that the object existed to drop. If we miss writing the check and if the object is not available, then we are being served with an error message. With SQL Server 2016, now, we can check the existence of the respective object without any error being generated.

Need of Checking

The obvious question that may come to the mind of those who haven't used DROP statement much, is- "Do I really need to check if the object exists prior to executing the DROP statement?"

Well, the answer is a big YES.

Why? Let's quickly test that and see what we get in the result. Execute one of the following statements.

--(1)
DROP Database ARandomDatabaseForDemo
GO
--(2)
DROP TABLE ARandomTableForDemo
GO
--(3)
DROP PROCEDURE ARandomProcForDemo
GO
--(4)
DROP FUNCTION ARandomFunctionForDemo
GO
--(5)
DROP TRIGGER ARandomTriggerForDemo
GO

Note - Execute only if you don't have the respective objects. It will throw an exception right away, as follows -

Cannot drop the database/table/procedure/function/trigger 'ARandom[Database/Table/Procedure/Function/Trigger]ForDemo', because it does not exist or you do not have permission.

Now, in another case, let's say we have a table but not the column or any attribute/constraint we want to drop.

ALTER TABLE [dbo].[Registration] DROP COLUMN AnyColumn
GO

We'll get an error message like,

ALTER TABLE DROP COLUMN failed because column 'AnyColumn' does not exist in table 'Registration'.

In most cases, we may not want to have an error message thrown at us. We would rather like to skip the error message and move forward to execute the rest of the logic in the script. The very common example is when we want to create a table in the database, we generally do a check for if the table exists in that particular database or not and if it exists, then we go ahead to drop it and create the table with the latest structure. This kind of situation generally appears while we are in the initial phase of development or designing our database and multiple resources working on the same project.

The Old Way

Earlier, we were writing a long IF EXISTS statement to check if the respective object exists or not, followed by the DROP statement.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = _
OBJECT_ID(N'[dbo].[ARandomTableForDemo]') AND type in (N'U'))
DROP TABLE [dbo].[ARandomTableForDemo]
GO

Or,

IF OBJECT_ID('[dbo].[ARandomTableForDemo]', 'U') IS NOT NULL
DROP TABLE [dbo].[ARandomTableForDemo]
GO

The New, Easier & Better Way

Let's rewrite the DROP statement with the newer syntax.

Syntax

DROP DATABASE [ IF EXISTS ] database_name

Example

DROP DATABASE IF EXISTS ARandomDatabaseForDemo
GO

Isn't it much straight and easy?

Let's look at the complete list of DROP statements which we have written without any check for existence, by applying the new method of DROP IF EXISTS.

--(1)
DROP Database IF EXISTS ARandomDatabaseForDemo
GO
--(2)
DROP TABLE IF EXISTS ARandomTableForDemo
GO
--(3)
DROP PROCEDURE IF EXISTS ARandomProcForDemo
GO
--(4)
DROP FUNCTION IF EXISTS ARandomFunctionForDemo
GO
--(5)
DROP TRIGGER IF EXISTS ARandomTriggerForDemo
GO

And, if you want to DROP a column or constraint, you can do so like:

ALTER TABLE [dbo].[Registration] DROP COLUMN IF EXISTS AnyColumn GO

Comparison With Other Competitors

MySQL

MySQL already provides such feature to include this optional clause in the DROP statement.

DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]

Reference

http://dev.mysql.com/doc/refman/5.7/en/drop-table.html

PostgreSQL

PostgreSQL too already has the same feature.

DROP TABLE [ IF EXISTS ] tbl_name [, ...] [ CASCADE | RESTRICT ]

Reference

https://www.postgresql.org/docs/8.2/static/sql-droptable.html

Oracle

No such option is available so far. I have checked the documentation and could not find any such mention. So, the option is to either,

Check the existence of the object prior to DROP
Catch the exception and handle

Conclusion

This is really a great inclusion to the feature list in Data Definition Language (DDL) for SQL Server 2016. If you found this helpful, please share your feedback.

3 Comments

Join the conversation! Your thoughts help the community grow.

Nigel Fernandes8y ago
Can this be used with an ALTER statement as well ?
- Nigel Fernandes8y ago
  Thats cool then ...
- Suvendu Shekhar Giri8y ago
  Yes. It can be used with ALTER statement as well. Example : ALTER TABLE MyTable DROP COLUMN MyColumn