Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Droping a temp table. Expand / Collapse
Author
Message
Posted Thursday, January 17, 2013 3:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:40 PM
Points: 248, Visits: 569
Hi all,

I tried deleting a temp table using the below following code

IF EXISTS(SELECT * FROM sys.tables where name LIKE '#TEMP')
DROP TABLE #TEMP

It didn't work, then i tried this below code (which i found on one of good blog)

IF EXISTS(SELECT * FROM sys.tables where name LIKE '#TEMP%')
DROP TABLE #TEMP

Still it doesn't worked, then i tried this below code

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #TEMP

and it work. I am not able to figure it out why does the above 2 scripts did not work.
Post #1408292
Posted Thursday, January 17, 2013 3:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,467, Visits: 35,535
Firstly, because temp tables are in tempDB, and unless you are in that database, sys.tables refers to your user database's tables, not TempDB.

Secondly, because the name in the system catalog is not #Temp. It'll be something like #Temp________________________________8FE34A. Hence your first one won't work.

The second risks false positives. If another connection has a temp table named #TempAccount, your exists would pick it up, return true and then the
drop will fail because there's no temp table #Temp on your connection. Again, that's if run while the connection is using TempDB.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1408295
Posted Thursday, January 17, 2013 3:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:40 PM
Points: 248, Visits: 569
Thanks Gila for quick reply.
Post #1408303
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse