Check If Temporary Table or Temp Table Exists in SQL Server Database
SQL Server database programmers frequently create temporary tables and before creating temp table, T-SQL developer has to drop temp table after they validate temp table already exists on the database.
SQL Server developers test Object_Id() of a database object and if it is not null then execute Drop Table command as seen in following SQL example.
Let's create a test table in your development environment and then try to select object_id() for this new database table. If the queried object_id() is not null, SQL programmers can execute "DROP TABLE" command without any error.
create table TestTable(id int)
if object_id('TestTable','U') is not null
drop table TestTable
Database developers can read SQL tutorial DROP Table If Table Exists Command on a SQL Server Database for methods used to test the existence of a database table on SQL Server.
Of course, it is not as easy to check the object_id() of a temp table. It is some more tricky.
Let's work on a sample case.
create table #TestTable(id int)
select object_id('#TestTable','U')
It will return NULL although we have already created our temp table. SQL developers cannot execute the same CREATE TABLE statement for the same temp table second time. Probably following SQL error will be thrown after you try to test temp table existence using the way for normal database tables.
Msg 2714, Level 16, State 6, Line 4
There is already an object named '#TestTable' in the database.
Actually the reason is easy to explain.
The temporary tables (either local temporary tables or global temp tables) they're all created in tempdb system database.
If T-SQL developer query tempdb database system tables for the temporary table name, by using a SQL Select statement as follows temporary tables can be identified.
select * from tempdb.sys.tables where name like '#TestTable%'
Here is the output showing that target temp table is created in tempdb system database on SQL Server instance.
So the correct way to get the object_id of a temp table is as follows:
select object_id('tempdb..#TestTable','U')
Local temp table object_id values are negative. On the other hand global temporary tables have positive object_id values
So to summarize, the correct way of creating a DROP IF EXISTS script should be as follows for temp tables.
if object_id('tempdb..#TestTable','U') is not null
drop table #TestTable
create table #TestTable(id int)