Click here to Skip to main content
14,242,971 members

DROP IF EXISTS in SQL Server 2016

Rate this:
4.02 (12 votes)
Please Sign up or sign in to vote.
4.02 (12 votes)
3 Nov 2015CPOL
SQL Server 2016 introduces the new DROP IF EXISTS syntax

New Statements in SQL Server 2016

One of the most annoying things in SQL Server is checking whether an object exists before it is dropped:

IF OBJECT_ID('dbo.Product', 'U') IS NOT NULL
 DROP TABLE dbo.Product;
 
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'trProductInsert')
 DROP TRIGGER trProductInsert

SQL Server 2016 CTP3 adds new DROP IF EXIST (or DIE) syntax:

DROP FUNCTION IF EXISTS fnCount
DROP PROCEDURE IF EXISTS spReport
DROP TABLE IF EXISTS myTable

In SQL Server 2016 CTP3, the following objects can DIE:

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Jovan Popovic(MSFT)
Program Manager Microsoft
Serbia Serbia
Graduated from Faculty of Electrical Engineering, Department of Computer Techniques and Informatics, University of Belgrade, Serbia.
Currently working in Microsoft as Program Manager on SQL Server product.
Member of JQuery community - created few popular plugins (four popular JQuery DataTables add-ins and loadJSON template engine).
Interests: Web and databases, Software engineering process(estimation and standardization), mobile and business intelligence platforms.

Comments and Discussions

 
GeneralMy vote of 4 Pin
Suvendu Shekhar Giri5-Nov-15 9:41
professionalSuvendu Shekhar Giri5-Nov-15 9:41 
QuestionSure would be nice if they would add something like DIE(@ObjectType, @ObjectName, @ReturnValue) Pin
YSLGuru3-Nov-15 4:51
memberYSLGuru3-Nov-15 4:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Tip/Trick
Posted 3 Nov 2015

Tagged as

Stats

11K views
7 bookmarked