Click here to Skip to main content
15,304,269 members
Articles / Database Development / SQL Server / SQL Server 2016
Technical Blog
Posted 23 Aug 2016

Stats

18.6K views
7 bookmarked

DROP IF EXISTS in SQL Server 2016 (DIE)

Rate me:
Please Sign up or sign in to vote.
4.74/5 (10 votes)
24 Aug 2016CPOL
DROP IF EXISTS in SQL Server 2016 (DIE)

Prior to SQL Server 2016, when we need to drop a SQL Object, it's the best practice to check whether the respective object exists or not. Otherwise, the operation will return in an error.

SQL
DROP TABLE [SomeTable]

If the object is not found, it will return the following error:

Msg 3701, Level 11, State 5, Line 11
Cannot drop the table 'SomeTable', because it does not exist or you do not have permission.

Hence, we need to change the syntax as:

SQL
IF EXISTS(SELECT 'x' FROM sys.objects AS O WHERE O.name = 'SomeTable' AND O.[type] = 'U')
    DROP TABLE [SomeTable]

--OR

IF OBJECT_ID('dbo.SomeTable','U') IS NOT NULL
    DROP TABLE [SomeTable]  

In SQL Server 2016, there is an easier way to do this using comparatively less amount for coding.

SQL
DROP TABLE IF EXISTS [SomeTable];
DROP PROCEDURE IF EXISTS [SomeProcedure];

Even this can be used when dropping columns and constraints from a table.

SQL
ALTER TABLE [TableName] DROP CONSTRAINT IF EXISTS [ConstraintName]
ALTER TABLE [TableName] DROP COLUMN IF EXISTS [TableName]

Example:

SQL
CREATE TABLE SomeTable(
    Id        INT
    ,Name    VARCHAR(10)        NOT NULL CONSTRAINT [DF_SomeTable_Name] DEFAULT ('')
)

ALTER TABLE dbo.SomeTable
DROP CONSTRAINT IF EXISTS [DF_SomeTable_Name]

ALTER TABLE dbo.SomeTable
DROP COLUMN IF EXISTS [Name]

The beauty of this functionality is that even the object does not exist, it will not fail and execution will continue.

Currently, the following objects can be dropped with the DIE functionality:

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

Hope this will be useful to you.

License

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

Share

About the Author

Manjuke Fernando
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.

My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.

My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.


Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports

Comments and Discussions

 
QuestionTemp tables Pin
Rarelyamson24-Aug-16 4:56
MemberRarelyamson24-Aug-16 4:56 
AnswerRe: Temp tables Pin
Manjuke Fernando25-Aug-16 20:25
professionalManjuke Fernando25-Aug-16 20:25 
Yes.. Even the local and global temp tables can be dropped using this.

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.