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:
DROP TABLE IF EXISTS <Temp-Table-Name>
DROP TABLE IF EXISTS #TempTab
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
DROP TABLE #TempTable
CREATE TABLE #TempTable ( ID INT, Name VARCHAR(100) )
- About DIY or DROP IF EXISTS at Microsoft Docs.