June 9, 2010 at 1:46 pm
Hi folks,
This sounds like a newbie question but I just don't understand it:
is there anything wrong with these query:?
IF EXISTS(SELECT name FROM sys.TABLES WHERE name like '#temp%' AND type='U')
DROP TABLE #temp
IF EXISTS(SELECT name FROM sys.objects WHERE name like '#temp%' AND type='U')
DROP TABLE #temp
The #temp won't be deleted because SELECT name FROM sys.TABLES WHERE name like '#temp%' AND type='U' won't get it, however it DOES EXISTS because I got an error when I tried to drop it.
Msg 2714, Level 16, State 6, Line 1
There is already an object named '#temp' in the database.
What's the problem for the IF query?
Thank you in advance.
June 9, 2010 at 1:48 pm
select object_id('#temp') returns NULL
June 9, 2010 at 2:16 pm
Temp tables (created with #) are actually stored in the tempdb database, not in whatever database you're currently working in. I believe the sys.objects and sys.tables system views only look at the 'current' database.
You could look in tempdb.dbo.sysobjects for the name, but if other users/sessions had created a similar temp table, you might get a positive result on your lookup when, in fact, that temp table wasn't 'yours'.
Rob Schripsema
Propack, Inc.
June 9, 2010 at 2:17 pm
You are right, thank you.
June 9, 2010 at 2:27 pm
You can use the following to check for existence of the table:
if OBJECT_ID('tempDB..#temp','U') is not null
DROP TABLE #temp
Rob Schripsema
Propack, Inc.
June 9, 2010 at 2:46 pm
be aware that the temp table will not simple be called #whatevernameyougive but in the form of
##whatevernameyougive_______________________________________________________________________________________________________________00000000957C
in sys.objects so you will need to use a like statement
Andrew
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply