Dropping temp tables (when they dont want to drop themselves)
-
The code below checks for, and deletes, the appropriate table, how do I do the SAME THING - except with a temporary table? Is it the same code?
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tablename]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tablename]
-
My first question would be why the temp tables are not being dropped when the connection is closed? The statement you list will work with the following changes:
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb.[dbo].[tablename]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table tempdb.[dbo].[tablename]
If you do not add tempdb to the table name when running the object_id function you get null because the table does not exists in the current database.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Apologies for disagreeing with the masters...
Just changing the "if exists" to tempdb (as previously posted) will NOT detect or delete a temp table from tempdb UNLESS you know the full name! And that is NOT likely! When you do something like:
CREATE TABLE #MyTest (
ID INT,
SOMETHING VARCHAR(50))
go
The following will NOT delete the temp table...
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'[dbo].[#MyTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#MyTest]
GO
...BECAUSE the name of the temp table is actually a 132 byte name consisting of the user name, the table name used during the CREATE, a wad of underscores, and a unique/constantly changing hexadecimal number. It looks like this...
dbo.#MyTest_____________________________________________________________________________________________________________000000005D55
p.s. By "constanly changing" I mean, the number is a different number each time you create the tempory table. If you want to ensure the table does not exist, just use the DROP TABLE command and trap the error so it doesn't throw you out. Normally, only Query Analyzer keeps the table active until you close the QA window that used the table. For development purposes, write a DROP TABLE command, comment it out with a couple of dashes, and when you need to drop the table, highlight the command (without the dashes) and execute it.
--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) -
This works for me.
if object_id( 'tempdb..#MyTempTable' ) is not null drop table #MyTempTable
We Don't need to specify that the object is a table, so ignore that stuff usually in Generated SQL from Enterprise Manager.
I never give it an object owner name, and just leave that blank (though it 'aught' to work with dbo).
Since in our name space at execution time it doesn't appear to have that postfixed uniqueness stuff tacked on to the name, you don't have to add it.
-
This is the best way. I see a lot of folks do a look in sysobjects for the temp table name but if on another connection the name is in use it will get an error if doesn't exist for this one. Using object id as described will only return non-null when it has been created on this connection.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply