How To Drop Temporary Table If Exists In SQL Server?

In my earlier article, I have explained how to drop database objects including regular tables only if exists. In that article, unfortunately, I have not mentioned about temporary or temp tables. Now we will see how to drop temporary table if exists in the server.

In SQL Server 2016 And Higher

In SQL Server 2016, Microsoft introduced DIY or DROP IF EXISTS functionality. By adding IF EXISTS to the drop statement, you can drop the object only when it exists in the database. You can use DROP IF EXISTS to drop any temporary table as well if it exists. Let’s see how to use it:

Syntax

DROP TABLE IF EXISTS <Temp-Table-Name>

Example

DROP TABLE IF EXISTS #TempTab
GO

In SQL Server 2014 And Lower Versions

Older versions of SQL Server does not have DIY or DROP IF EXISTS functionality. So, we have to use the old technique of checking for the object using OBJECT_ID. Let’s see how to use it.

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL 
BEGIN 
    DROP TABLE #TempTable 
END

CREATE TABLE #TempTable ( ID INT, Name VARCHAR(100) )
GO

Reference

• About DIY or DROP IF EXISTS at Microsoft Docs.