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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.

download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012

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)

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums

SQL Split String Related SQL Server Tutorials

Split String using XML - How to Convert or Split a Delimited String Values to Rows using T-SQL XML Commands
Split String Into Fixed Length Pieces in SQL
Recursive Function Sample - MS SQL Server Recursive T-SQL Split Function
TSQL Character Split Function in SQL Server
Case Sensitive SQL Split Function
SQL Server 2016 Split String Function STRING_SPLIT
SQL Server String Split T-SQL CLR Function Sample
SQL Server CLR Split String Function for 2-Dimensional Array
T-SQL Split User Defined Function

Copyright © 2004 - 2018 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems