The Complete Beginner’s Guide to DROP IF EXISTS in SQL SERVER

DIE a new Feature in SQL SERVER 2016

In the Series of SQL SERVER 2016 journey, this is our new article. In this article, we are sharing a new cool feature which introduced in SQL SERVER 2016 which is DROP IF EXISTS (DIE) .

In our development many times it happens that we need to drop a table and as a best practice we write the following syntax as shown in below figure

 

TraditionalWay_Indiandotnet

 

Now, in SQL SERVER 2016 the same task is super easy. You can write the following syntax to drop the table object

 

DROP_TABLE_IF_EXISTS_INDIANDOTNET

 

DROP TABLE IF EXISTS TABLENAME

 

The best part is if suppose the object does not exist thenĀ  here will be no error execution will continue.

Let me share one more example of Dropping a stored procedure.

 

DROP_PROCEDURE_IF_EXISTS_INDIANDOTNET

 

Similar, way we can write for following data objects and with the following syntax

 

Procedure:-
DROP PROCEDURE IF EXISTS Procedure Name

 

Assembly:-
DROP ASSEMBLY IF EXISTS Assembly Name

 

ROLENAME :-
DROP ROLE IF EXISTS ROLENAME

 

TRIGGER :-
DROP TRIGGER IF EXISTS Trigger Name

 

VIEW:-
DROP VIEW IF EXISTS View Name

 

RULE:-
DROP RULE IF EXISTS RULENAME

 

Type:-
DROP TYPE IF EXISTS Type Name

 

Database:-
DROP DATABASE IF EXISTS Database Name

 

Schema:-
DROP SCHEMA IF EXISTS Schema Name

 

User:-
DROP USER IF EXISTS Username

 

SECURITY POLICY:-
DROP SECURITY POLICY IF EXISTS Policy Name

 

View :-
DROP VIEW IF EXISTS View Name

 

FUNCTION:-
DROP FUNCTION IF EXISTS Function Name

 

SEQUENCE:-
DROP SEQUENCE IF EXISTS Sequence Name

 

INDEX :-
DROP INDEX IF EXISTS Index Name ON Table Name

 

Synonym:-
DROP SYNONYM IF EXISTS Synonym Name

 

I like this feature I am sure you will also like this.

 

Please, do share your feedback for blog post.

 

Enjoy !!

Comments

  1. Stephen Mangiameli

    Meh. It doesn’t save that many key clicks. We typically don’t drop much around here anyway to maintain the data integrity and current permissions. Instead we perform CREATE of stub and then ALTER like so…

    IF OBJECT_ID(‘tbl_Example’,’U’) IS NULL
    EXEC sp_executesql N’CREATE TABLE tbl_Example (RowID INT)’

    ALTER TABLE tbl_Example ALTER COLUMN …
    ALTER TABLE tbl_Example ADD …
    ALTER TABLE tbl_Example DROP COLUMN …

    We do the same thing for other objects to maintain permissions.

    IF OBJECT_ID(‘usp_Example’,’P’) IS NULL
    EXEC sp_executesql N’CREATE PROC usp_Example AS SELECT 1 AS STUB’

    ALTER PROC usp_Example
    AS

    1. Malcolm

      Stubs are not beginner level, but the IF EXISTS syntax can be applied to those constructs too. I do miss Oracle’s CREATE OR ALTER…

  2. Sidhu

    i have a question.Could you give me solution for following problem?
    CREATE A TRIGGER WITH NAME SALTRANSTRIGGER THAT UPDATES OR INSERTS THE SALARY OF EMPLOYEE AS A TRANSACTION. IF SALARY INSERTED OR UPDATED IS LESS THAN 1000, THEN THE TRANSACTION MUST BE ROLLED BACK.

    i tried but getting an error when i try ti RAISERROR:
    Msg 3609, Level 16, State 1, Line 1
    The transaction ended in the trigger. The batch has been aborted.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

code