Check if temporary table exists or not?

Question

Check if temporary table exists or not?

Viewing 15 posts - 31 through 45 (of 48 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

But when I tried to call a stored procedure to created the #temp table, that table name was not visible to subsequent statements within the same query. I can't find it now, but I think that was documented somewhere in BOL, so I just sighed and took it as a given.

You did something wrong there.

Because I use to reference #table from procedures all the time. It never fails.

Even when I call a SP in another database.

BUT! It should be local call.

Remote calls (from SQL Server or another application, like cmd) require ##tables.

_____________
Code for TallyGenerator

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 2

Sergiy -

I'll try to post my example that (I thought) failed tomorrow. Thanks for your interest and comment.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Jim Russell (1/10/2008)
I can't find it now, but I think that was documented somewhere in BOL, so I just sighed and took it as a given.

Is this what you're thinking of?

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.

It's under CREATE TABLE in the 2005 books online. Don't have SQL 2000 docs on this machine.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 4

Exactly, Gail. Thanks!

And since it sorta of matched what I was trying to do (calling a stored procedure to define the #temp table, and then referencing that #table name farther down within the calling query), I decided I had run afoul of some MS commandment. And I think that qualifies as running under the same connection.

But since Sergiy seems able to make it work, I'll investigate more in the AM.

GSquared SSC Guru Points: 260824 More actions · Answer 5

If you want to do that, create a temp table with ## instead of #, and then make sure to drop it when you're done with it. Those can be accessed by multiple connections.

The advantage is they can be accessed by multiple connections. The disadvantage is they can be accessed by multiple connections. In other words, if a second user causes the same query to be run, it will access the same temp table.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

Jim Russell (1/10/2008)
Jeff, once I thought there was at least one more exception to the rule:
"Each new query window creates it's own connection or "session" to the database."
From a multiple statements within one "connection" (query editor window), I could create a #temp table with one statement, and reference that same #temp table in subsequent statements ok.
But when I tried to call a stored procedure to created the #temp table, that table name was not visible to subsequent statements within the same query. I can't find it now, but I think that was documented somewhere in BOL, so I just sighed and took it as a given.

Local temp tables created in a stored procedure go away when the store procedure completes.

However, if you call another stored procedure inside the first procedure, it will be able to see the temp tables created by the stored procedure that called it. It is a good thing to remember, because it can be a source of hard to identify bugs.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

Michael is correct, as well... if you create a temp table in a nested proc, the outer proc will not be able to see it. If you create a temp table in the outer proc, inner procs will be able to see it. As Michael points out, that can sometimes be very difficult to trouble-shoot.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 8

And here is another good reason for existence check.

Because inner SP depends on #table it should terminate execution immediately if outer SP failed to create #table or inner SP got called from a wrong place.

_____________
Code for TallyGenerator

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 9

The caller not being able to see a #temp created by a callee would seem reasonable, like a local variable scoping rule (but not connection related.) I'm not sure that is documented as well as it should be, but since I miss even the things that are documented clearly, I won't complain.

Thanks very much for everyone's input on this!

Joe B-478020 SSC Eights! Points: 966 More actions · Answer 10

SQL 2000 Books Online:

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.

All other local temporary tables are dropped automatically at the end of the current session.

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 11

Thanks, you are right.

(I'm glad I added the CYA "but since I miss even the things that are documented clearly..." above.) (CYA s/b CMA.)

Arun T Jayapal Say Hey Kid Points: 707 More actions · Answer 12

I hope all temporary tables are session specific. Actually I prefer them to be connection specific...are they?

So will the following statement reveal the connection specific information? If multiple people are running a procedure, will the following code evaluate to true...?

if exists (select * from tempdb..sysobjects where id = object_id('tempdb..#temptbl'))

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 13

Arun T Jayapal (1/15/2008)
I hope all temporary tables are session specific. Actually I prefer them to be connection specific...are they?
So will the following statement reveal the connection specific information? If multiple people are running a procedure, will the following code evaluate to true...?
if exists (select * from tempdb..sysobjects where id = object_id('tempdb..#temptbl'))

Only if you have a object named #temptbl

Arun T Jayapal Say Hey Kid Points: 707 More actions · Answer 14

GilaMonster (1/10/2008)
Arun T Jayapal (1/10/2008)
After that if condition I tried to drop the table. It said some syntax before drop. I believe you can truncate/drop temporary tables?
Could you post the exact code that you tried, with the drop statements in it.
You can drop and truncate temp tables. They're treated almost the same as normal tables.

This issue is resolved. I forgot to put a "begin" and "end" after the if condition. Plus I was trying to find the above reply multiple times to follow up. Didn't know this topic would expand to a lot of replies.

Ian Yates SSCoach Points: 19738 More actions · Answer 15

Having read through all of the messages, it seems like some PMs were sent to explain things rather than putting them on the forum. My 2c and summary 🙂

Connection pooling is completely transparent and does indeed get rid of temp tables. I cannot remember the exact stored proc name, but if you use connection pooling and SQL Profiler you'll see a sp_reset_connection (or something to that effect) executed by your application. The one thing that I think fouls up connection pooling is application roles (don't quote me on it though)

Session & Connection are one and the same.

Temporary tables created with a single # prefix are specific to the connection on which they were created. Other connections cannot see them.

Temporary tables created with a double ## prefix are global to all on the server. I'm not sure when they drop out of scope (it's probably specified in books online).

Each new window in SQL Query Analyzer / SSMS is a new connection. The object browser also has a connection of its own (as do other things in those programs).

I've never used MARS but I suspect the multiple queries in a single connection that are possible in this case would share the temporary tables. Anyone know? Anyone even bother using MARS?

Finally, as discussed, temp tables are scoped so that if they're created from within a stored proc they're automatically destroyed at the end of that proc and thus are only visible to nested procs that are called. If you create a temp table outside of a stored proc (eg in a QA/SSMS window) then it's visible for the duration of the connection/session unless dropped earlier.

The code someone posted for checking for the existance of a temp table using select * from tempdb... like 'mytable%' may not work as expected because it will pick up similarly named temp tables from other connections causing the drop statement on the subsequent line to fail.

That's about it 😀