SQL Server 2016 – Drop If Exists

SQL Server 2016 has introduced many new features. One of them is Drop If Exists (DIE) , a DDL statement, which can be used to drop the objects if exists

In earlier version, you need query on the system functions or objects

Suppose you have a table named test and you want to drop it if it already exists in the database. In earlier versions, you can do

IF OBJECT_ID('test','U') is not null
DROP TABLE test;

Now from version 2016 onwards, you do not need an IF condition to find the existence of the table, use DROP IF EXISTS

DROP TABLE IF EXISTS test

It Drops the table if exists otherwise do nothing

You can also do the same thing for other objects as well like TRIGGER, PROCEDURE, etc

— To drop the Trigger named tr_test

DROP TRIGGER IF EXISTS tr_test

— To drop the Trigger named proc_test

DROP PROCEDURE IF EXISTS proc_test

This is really a handy feature which is also available in MySQL

Advertisements

4 thoughts on “SQL Server 2016 – Drop If Exists

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s