DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. DROP IF EXISTS: A Cool Feature in SQL Server 2016

DROP IF EXISTS: A Cool Feature in SQL Server 2016

This article will cover a timesaver for those using SQL Server. Need to drop a table? A single statement can handle that without gumming up the works if it's not there.

Rajat Jaiswal user avatar by
Rajat Jaiswal
·
Oct. 19, 16 · Tutorial
Like (4)
Save
Tweet
Share
11.87K Views

Join the DZone community and get the full member experience.

Join For Free

In this article, we're going over a cool feature which was introduced in SQL Server 2016. That feature is DROP IF EXISTS (DIE) .

During development, we often need to drop a table. As a best practice, we generally use the syntax below.

TraditionalWay_Indiandotnet

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

DROP_TABLE_IF_EXISTS_INDIANDOTNET

DROP TABLE IF EXISTS <TABLE NAME>


And the best part? Suppose the object does not exist. That's a problem, right? Nope. Remember "IF EXISTS?" In that event, there will be no error, and the execution will continue.

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

DROP_PROCEDURE_IF_EXISTS_INDIANDOTNET

DROP PROCEDURE IF EXISTS <PROCEDURE NAME>

 

But Wait, There's More!

Similarly, we can handle the following data objects with the same syntax.

Assembly

DROP ASSEMBLY IF EXISTS Assembly Name 

Role

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 

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, and I'm sure you will, too. Please share your feedback, and enjoy!

Drops (app) sql

Published at DZone with permission of Rajat Jaiswal, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Microservices Discovery With Eureka
  • SAST: How Code Analysis Tools Look for Security Flaws
  • Automated Performance Testing With ArgoCD and Iter8
  • Top Authentication Trends to Watch Out for in 2023

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: