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:

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