SQL Server – T-SQL Enhancement “Drop if Exists” clause

I was reading through some of the enhancements introduced with SQL Server 2016 and I stumbled upon an interesting addition. The T-SQL construct introduced has been something very common and on the pending request for years I suppose.

So what are am I talking about? I do a number of demo’s for conferences and I have shared a number of script for it. One of the script you will find starting of any of my such demo is to drop any objects that were inadvertently left from my previous run.

-- Typical Script
IF OBJECT_ID('[Person].[CountryRegion]', 'U') IS NOT NULL
DROP TABLE [Person].[CountryRegion];
-- Another variation of the Drop script
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'trg_PersonAddress')
DROP TRIGGER trg_PersonAddress

I am sure this is nothing strange. Now with SQL Server 2016, the same script can be rewritten as:

-- Updated Script with SQL Server 2016
DROP TABLE IF EXISTS [Person].[CountryRegion];
DROP TRIGGER IF EXISTS trg_PersonAddress;

It was a pleasant surprise to see this construct added to SQL Server and I thought to bring this to you as soon as possible. The best part is, if the object doesn’t exist, this will not send any error and the execution will continue.

This construct is available for other objects too like:

As I was scrambling the other documentations like ALTER TABLE, I also saw a small extension to this capability. Now consider the following table definition:

USE tempdb
GO
CREATE TABLE t1 (id INT IDENTITY CONSTRAINT t1_column1_pk PRIMARY KEY,
Name VARCHAR(30), DOB Datetime2)
GO

Now I can use the following extension of DROP IF EXISTS like:

ALTER TABLE t1 DROP CONSTRAINT IF EXISTS t1_column1_pk;
ALTER TABLE t1 DROP COLUMN IF EXISTS ID;

As I try to wrap up, how cool is this for you? Will you be using them? Do let me know.

Reference: Pinal Dave (https://blog.sqlauthority.com)