Hi,
I searched and searched and searched but can't seem to find a way to detect
if a global temporary table exists before I drop it. I'm sure it is
something simple and stupid that I missed, please help.
Thanks,
Bob
by Bob Ng » Thu, 16 Sep 1999 04:00:00
I searched and searched and searched but can't seem to find a way to detect
if a global temporary table exists before I drop it. I'm sure it is
something simple and stupid that I missed, please help.
Thanks,
Bob
by Stephen Robinso » Thu, 16 Sep 1999 04:00:00
Why would you want to drop a global temp table - when everyone has finished
with it it will be dropped anyway ? the way to detect if one or any exist
would be
select name from tempdb..sysobjects where name like '##%' and type = 'U'
or
select name from tempdb..sysobjects where name like '##test%' and type = 'U'
or
/* SQL 7.0 only */
use tempdb
go
select object_id('##test')
go
if you are useing sql7.0 you dont need to use the wildcard if you know the
name in 6.5 you will see loads of extra chars after the name so you will.
I hope this was of use.
Stephen Robinson (SQL MVP)
>I searched and searched and searched but can't seem to find a way to detect
>if a global temporary table exists before I drop it. I'm sure it is
>something simple and stupid that I missed, please help.
>Thanks,
>Bob
by Bob Ng » Thu, 16 Sep 1999 04:00:00
Thanks, your tip was right on the money! I want to drop the global temp
table because I'm calling the SP from ASP and I have connection pooling
turned on, so I'm not sure the global temp table will be dropped if I close
the connection from ASP.
Bob
> Why would you want to drop a global temp table - when everyone has
finished
> with it it will be dropped anyway ? the way to detect if one or any exist
> would be
> select name from tempdb..sysobjects where name like '##%' and type = 'U'
> or
> select name from tempdb..sysobjects where name like '##test%' and type =
'U'
> or
> /* SQL 7.0 only */
> use tempdb
> go
> select object_id('##test')
> go
> if you are useing sql7.0 you dont need to use the wildcard if you know the
> name in 6.5 you will see loads of extra chars after the name so you will.
> I hope this was of use.
> Stephen Robinson (SQL MVP)
> >Hi,
> >I searched and searched and searched but can't seem to find a way to
detect
> >if a global temporary table exists before I drop it. I'm sure it is
> >something simple and stupid that I missed, please help.
> >Thanks,
> >Bob
1. check existance of a temporary table before drop this table
Hi!
Before droping a temporary table I want to check its existance.
Are there any ideas how to do this in an multiuser-environment?
When I tried it with "...name LIKE '#temptable%'..." in
sysobjects-table or TABLES-view, I 'see' all temporary tables
from the other users (sessions) too.
So I'm not sure, if my session has allready created its own
temporary table, so that I can drop it without getting an error.
The user could initiate per session more than one instance of
the software-routine which creates the temporary table.
So I have no chance to check out if it was the first time the
software-routine created the temporary table already.
When I tried to get an solution with the help of OBJECT_ID()
and OBJECT_NAME()
(e.g. "OBJECT_NAME(OBJECT_ID('tempdb..#temptable'))" ), then
OBJECT_ID() returns NULL in case of an temporary table, although
it has an ID in the sysobjects-table and if I call OBJECT_NAME() with
this ID it returns the right name.
Are there any hints or ideas?
best regards,
Dietmar
2. How to benchmark SQL and PL/SQL scripts??
3. Checking for the existance of a temporary table.
5. How to determine temporary table existance
6. PL/SQL: Returning list of values with function
7. Querying existance of temporary tables
8. New Era
9. Global temporary tables v pl/sql tables
10. detecting Temporary tables
11. Detecting Temporary tables.........
12. Detecting the existence of a temporary table
13. Detecting temporary tables