0

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?

Adam Bellaire
  • 108,003
  • 19
  • 148
  • 163
ninesided
  • 23,085
  • 14
  • 83
  • 107

2 Answers2

5

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)
)
Jonathan
  • 25,873
  • 13
  • 66
  • 85
0

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.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • it's a complication scenario to explain, it's all legacy apps and crazy requirements (incl. not using regular tables). Basically, I need an application to create the temp table (via execute immediate) and then use a stored procedure to populate it. Then have the application read from the table. WTF. – ninesided Oct 14 '08 at 23:15
  • In that case, the best thing I can suggest is to try to SELECT from the table and see if it barfs! Sorry... – Marc Gravell Oct 15 '08 at 07:04
  • thanks, that's what I'm doing at the moment, but it feels a little inelegant. – ninesided Oct 15 '08 at 23:12