Post reply

Droping a temp table.

  • January 17, 2013 at 3:43 am

    #272845

    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.

  • January 17, 2013 at 3:45 am

    #1578204

    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, MVP, M.Sc (Comp Sci)
    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

  • January 17, 2013 at 3:54 am

    #1578211

    Thanks Gila for quick reply.

Viewing 3 posts - 1 through 2 (of 2 total)

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