Skip to content
Home » Testing for Object Existence: DROP … IF EXISTS

Testing for Object Existence: DROP … IF EXISTS

  • by

For the longest time, T-SQL writers have had to wrestle with ways of testing for an object’s existence so that it can either be dropped and recreated, or modified as needed.

This is especially common in the case of temp tables or table variables. If the object already exists when our script runs, the script will fail and leave our workflow in an inconsistent state.

Consider this script:

IF OBJECT_ID('tempdb..#doTheThing') IS NOT NULL
DROP TABLE #doTheThing;

CREATE TABLE #doTheThing (
ImportantColumn1 BIGINT,
ImportantColumn2 TINYINT,
ImportantColumn3 NVARCHAR(255)
);

With SQL Server 2016, we can now do this:

DROP TABLE IF EXISTS #doTheThing;

CREATE TABLE #doTheThing (
ImportantColumn1 BIGINT,
ImportantColumn2 TINYINT,
ImportantColumn3 NVARCHAR(255)
);

DROP ... IF EXISTS can be used on many objects, including DATABASE, FUNCTION, INDEX, PROCEDURE, ROLE, SCHEMA, SEQUENCE, SYNONYM, TABLE, TRIGGER, TYPE, USER and VIEW.

Share your object existence check nightmares with me on Twitter at @bornsql.

Tags: