dcsimg
 

Verifying Object Exists and Dropping Object with One Statement

Monday Mar 2nd 2020 by Gregory Larsen
Verifying Object Exists and Dropping Object with One Statement

Discover how to do both in one SQL Server statement: Verify that a SQL Server object exists and drop the object.

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

Home
Mobile Site | Full Site