Verifying Object Exists and Dropping Object with One Statement

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

Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles