dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted March 2, 2020

Verifying Object Exists and Dropping Object with One Statement

By Gregory Larsen

For years you probably have been writing code similar to the code below to verify an object exist prior to dropping it.

IF OBJECT_ID('dbo.MyObject', 'U') IS NOT NULL
    DROP TABLE dbo.MyObject;

Are you still doing this? Why? There is now a new way to do this that only requires a single statement.

With the introduction of SQL Server 2016 version Microsoft introduced the DROP IF EXISTS operation. Here is an example of how you can drop a table with just a single statement.

DROP TABLE IF EXISTS dbo.MyObject;

The DROP IF EXISTS method works to drop the following different object types:

AGGREGATE

PROCEDURE

TABLE

ASSEMBLY

ROLE

TRIGGER

VIEW

RULE

TYPE

DATABASE

SCHEMA

USER

DEFAULT

SECURITY POLICY

VIEW

FUNCTION

SEQUENCE

 

INDEX

SYNONYM

 

If you have been using that older two statement method to drop your SQL Server objects, then you might consider using the one statement method that was introduced in SQL Server 2016 next time you need to drop an object.

# # #

» See All Articles by Columnist Gregory A. Larsen



MS SQL Archives




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM