SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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.

temporary table in tempdb database sys.tables on SQL Server

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)

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.