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

SQL Server - T-SQL Enhancement "Drop if Exists" clause dropifexists

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:

AGGREGATESCHEMA USER
PROCEDUREDEFAULT
TABLESECURITY POLICY
ASSEMBLYVIEW
ROLEFUNCTION
TRIGGERSEQUENCE
VIEWINDEX
RULETYPE
DATABASESYNONYM

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)

SQL Scripts, SQL Server, SQL Server 2016
Previous Post
Interview Question of the Week #047 – Logic To Find Weekdays Between Two Dates
Next Post
SQL SERVER – XEvents to Find Cardinality Estimation Issues

Related Posts

10 Comments. Leave new

Leave a Reply