someone knows where is the information of the global tmep tables

Question

Post reply

someone knows where is the information of the global tmep tables

Felipe Saavedra

SSC-Addicted

Points: 477
More actions
January 27, 2005 at 2:18 pm

#163710

that's all
I want to drop a global temp table only if it's already created

Viewing 10 posts - 1 through 9 (of 9 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

use tempdb

if exists ( select * from information_schema.tables where table_name = '##YourTableName')

Drop Table ##YourTableName

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 2

Alternatively:

IF OBJECT_ID('tempdb..##stuff') is not null

DROP TABLE ##stuff

Avoids direct access to system tables. Supplemental checks for "is ##stuff actually a table" might be advisable. Also, while I haven't tested this, (a) you should only be able to drop it via the connection [spid] that made it, and (b) if any other connection is using it (within a transaction?) the drop should fail.

Philip

Felipe Saavedra SSC-Addicted Points: 477 More actions · Answer 3

thx both... i was missing the "use tempdb" statement when I try to search the table name in information_schema.tables

thx both of you

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

Just a minor clarification on 'global' temp tables. If it is truly global (i.e ## as stated) and not in a transaction, anyone may drop it. Just take query analyzer and in window 1 execute: create table ##temp (c1 int). The open another query analyzer pane or instance (there by creating another spid) and execute drop table ##temp.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Felipe Saavedra SSC-Addicted Points: 477 More actions · Answer 5

that's not the problem.. the problem was that I have multiple procedures, and I need to use the output table of another procedure, and in order to do that i create a global temp table. But I need to change te values of the table executing the procedure with other parameters. And I want to leave the drop table in the same procedure, that way I don't have to worry about that table.

So I finally use the answer in the second post, because USE it's not allow inside a procedure

PW-201837 SSC-Insane Points: 20805 More actions · Answer 6

Do you really need a global temp table then ?

If the purpose is just share data in a temp table between stored procedures, you only need a local temp table, declared in the outermost procedure.

Felipe Saavedra SSC-Addicted Points: 477 More actions · Answer 7

but the procedures are not nested, and a temp table It's destroyed when the create procedure finish

PW-201837 SSC-Insane Points: 20805 More actions · Answer 8

Ahh, I assumed nesting. You could always choose to nest, by creating 1 overall procedure that creates the local temp table, then calls the others in the same way that you are currently calling them.

gljjr SSChampion Points: 10080 More actions · Answer 9

You might also think about using a UDF for this. That way you don't have to create a global table and anytime the parameters are the same you should get the same results. Just a thought!

Of course this means you have to be running SQL2K.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.