Damir Matešić .blog

MS SQL DROP IF EXISTS (a.k.a. DIE)

By DM / Aug 03, 2018 / MS SQL
Facebook Twitter Google + LinkedIn Pinterest Email Share...

DROP IF EXISTS is a new MS SQL Server syntax extension introduced from MS SQL Server 2016.

You can remember a lot of situations when you need to drop an object (like table in production environment 😊). Ok, maybe not often in production but in some test/developer environments. For example when using temp tables or stored procedures there are often situations when you want to drop an object.

So, let’s try to delete a temporary table that we are just using for this demo.

Example:

DROP TABLE dbo.SQLNewFunctions;

 

"Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'dbo.SQLNewFunctions', because it does not exist or you do not have permission."

There is no such object in our database and we get the error message. Sometimes this is frustrating and in prior versions of MS SQL we could first check if the object exists and then drop it.

Example:

IF OBJECT_ID('dbo.SQLNewFunctions','U') IS NOT NULL DROP TABLE dbo.SQLNewFunctions

This is better but still there is more code to write before every DROP statement we have. We must also take care to match the object name correct twice (in the check part and in the DROP part).

So, now we have a new DROP IF EXIST statement which is basically a conditional DROP statement.

Still as before you can run it only if you have adequate permissions. If the object exists and you don’t have adequate permissions, you will get no error message. The reason for that is that the DROP IF EXIST simply suppress the error message.

Example:

DROP TABLE IF EXISTS dbo.SQLNewFunctions
DROP PROCEDURE IF EXISTS dbo.sp_SQLNewFunctions

We don’t know if the object was really removed (if we don’t have adequate permissions the object will not be removed) but there is no error 😊.

Currently supported objects types by DROP IF EXIST (DIE):

  • AGGREGATE
  • PROCEDURE
  • TABLE
  • ASSEMBLY
  • ROLE
  • TRIGGER
  • VIEW
  • RULE
  • TYPE
  • DATABASE
  • SCHEMA
  • USER
  • DEFAULT
  • SECURITY POLICY
  • VIEW
  • FUNCTION
  • SEQUENCE
  • INDEX
  • SYNONYM

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (443.00 bytes)