using Object_id() function with #tables

Question

I want to ensure if a temporary table exists in my database or not.

I tried to use OBJECT_ID() function but it seems that I can't use it with temporary tables.

How can I resolve this problem?

IF OBJECT_ID('tempdb..#table') IS NOT NULL DROP TABLE #table CREATE TABLE #Table? — Dibstar, Dec 06 '11 at 14:53
possible duplicate of [How to check if a temporary table is existing in Database](http://stackoverflow.com/questions/581427/how-to-check-if-a-temporary-table-is-existing-in-database) — Nix, Dec 06 '11 at 14:58
@Nix - `@table` = table variable. `#table` = temporary table. `##table` = global temporary table. — MatBailie, Dec 06 '11 at 15:02
Thank You all ! All the answers are write, now witch one will I Accept ?! — Alaa, Dec 06 '11 at 15:04
@Dems I dont understand your comment #table = temp table (as i said above) and its not a "hash table" that carries alternate meaning. — Nix, Dec 06 '11 at 15:05
@nix - Oh, maybe you were replying to a comment or text that is no longer there. I thought you meant `temporary table != #table`. My apologies. — MatBailie, Dec 06 '11 at 15:22

score 41 · Accepted Answer · answered Dec 06 '11 at 14:53

41

Use

OBJECT_ID('tempdb..#foo')

to get the id for a temporary table when running in the context of another database.

answered Dec 06 '11 at 14:53

Martin Smith

I tried this : select OBJECT_ID('tempdb.myDb.##myTempTable') But, It's not working – Alaa Dec 06 '11 at 14:57
5

Get rid of the `myDb` bit. That isn't in my answer! Use `OBJECT_ID('tempdb..##myTempTable')` or `OBJECT_ID('tempdb.dbo.##myTempTable')` – Martin Smith Dec 06 '11 at 14:57
I know this is an OLD thread, but I just wanted to point out the fact that the answer has two (2) periods between tempdb and the #table...Took me a few tries to catch that. – InfOracle Apr 29 '20 at 16:23
1

yeah - if you created the temp table in non default schema you would put the schema name between those two dots – Martin Smith Apr 29 '20 at 16:24

score 7 · Answer 2 · answered Dec 06 '11 at 14:58

When OBJECT_ID is called, for Temporary table/Hash table TEMPDB it must be specified unless it is already working database.

I check in SQL2008 and verify below.

USE SampleDB
create table #tt1 (dummy int)
select OBJECT_ID('SampleDB..#tt1')  -- returns NULL
select OBJECT_ID('tempdb..#tt1')   -- returns ID

score 2 · Answer 3 · answered Dec 06 '11 at 14:53

2

How do you check if a temp table exists?
You can use IF OBJECT_ID('tempdb..#temp') IS NOT NULL

--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END

answered Dec 06 '11 at 14:53

You should at least quote your source ;) http://sqlservercodebook.blogspot.com/2008/03/check-if-temporary-table-exists.html – Nix Dec 06 '11 at 14:56
@Nix - True. *1 more character to type before SO allows me to post my comment :)* – Lieven Keersmaekers Dec 06 '11 at 15:02

score 2 · Answer 4 · answered Dec 06 '11 at 14:53

2

Use this to change the context of the OBJECT_ID call to tempdb

OBJECT_ID('tempdb..#table')

OBJECT_ID on MSDN shows 3 part object names. In this case you can omit schema_name

OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ]

answered Dec 06 '11 at 14:53

gbn

4 Answers4