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
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 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.
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
That’s easy and nice.
Thanks
Good to learn one new thing.
It’s a smooth but very useful change.
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
…
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…
That’s great.
Thanks for sharing.
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.