Post
Ask Question

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. --(1)  
  2. DROP Database ARandomDatabaseForDemo  
  3. GO  
  4. --(2)  
  5. DROP TABLE ARandomTableForDemo  
  6. GO  
  7. --(3)  
  8. DROP PROCEDURE ARandomProcForDemo  
  9. GO  
  10. --(4)  
  11. DROP FUNCTION ARandomFunctionForDemo  
  12. GO  
  13. --(5)  
  14. DROP TRIGGER ARandomTriggerForDemo  
  15. 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.

  1. ALTER TABLE [dbo].[Registration] DROP COLUMN AnyColumn   
  2. 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.

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

Or,

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

The New, Easier & Better Way

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

Syntax

DROP DATABASE [ IF EXISTS ] database_name

Example

  1. DROP DATABASE IF EXISTS ARandomDatabaseForDemo  
  2. 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. --(1)  
  2. DROP Database IF EXISTS ARandomDatabaseForDemo  
  3. GO  
  4. --(2)  
  5. DROP TABLE IF EXISTS ARandomTableForDemo  
  6. GO  
  7. --(3)  
  8. DROP PROCEDURE IF EXISTS ARandomProcForDemo  
  9. GO  
  10. --(4)  
  11. DROP FUNCTION IF EXISTS ARandomFunctionForDemo  
  12. GO  
  13. --(5)  
  14. DROP TRIGGER IF EXISTS ARandomTriggerForDemo  
  15. GO  

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

  1. 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.

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

Reference

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

PostgreSQL

PostgreSQL too already has the same feature.

  1. 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.


Similar Articles