Testing for the existence of a temporary table in a multi tempdb environment?

Question

Is there any way of determining whether or not a specific temp table has been created in a session without referencing the tempdb database that it was created on? Users are allocated to a specific tempdb when they log in, so I don't know which tempdb they'll be using.

I don't need to specify a tempdb to select data out of the temp table, so surely I can see if the table exists?

Answer 1

It seems to me that you shouldn't be using temporary tables here... these seem more like regular tables (perhaps with a user identifier). What is the scenario here?

Note that using temporary tables in this way can play havoc with the optimiser/query cache - it will have to do lots of recompiles, as the meaning of #FOO changes pretty-much per call.

Answer 2

You can check for the existence of a temporary object using the object_id:

SELECT object_id('#tmpTable')

You can specify which tempdb you are using if you want, but you don't have to:

SELECT object_id('tempdb..#tmpTable')

It's worth noting that this also works for global temporary tables, too:

SELECT object_id('##tmpTable')

To make scripts re-runable without having to do a DROP TABLE at the end, I always do a check and a drop before I create a temporary table. I often leave tables created when I'm developing SQL code.

IF NULLIF(object_id('#tmpTable'), 0) IS NOT NULL DROP TABLE #tmpTable
CREATE TABLE #tmpTable (
    id int,
    col1 varchar(100)
)