Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35

    Unanswered: Drop a Temp Table

    Using SQL Server 2005. I have a stored prod that uses a temp table. I need to test at the start of the prod to see if the temp table is there. Using the following code at the start of my prod, but does not run.

    IF exists(select * from ##TO_STATUS_TBL)
    DROP TABLE ##TO_STATUS_TBL

    What is the best way to check and/or drop the temp table.
    Thank you, David

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You do not need to test if the temporary table is there. Temporary tables exist only within the scope of the procedure, and are automatically dropped when it completes. (OK, the exception would be global temporary tables, but you probably shouldn't be using those anyway....).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Looks like a global temporary table. I am not sure if it is strictly required, but if it is, how do you determine if other processes still need it?

    Anyway. This code should drop it every time
    Code:
    if exists (select * from tempdb..sysobjects where name like '##TO_STATUS_TBL%')
      begin
        drop table ##TO_STATUS_TBL
      end
    If I were you, I would look into whether the global part of the temp table is really needed. And if not, make this a local temp table.

    EDIT: Fixed typo in code.

  4. #4
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35

    Drop a Temp Table

    Thank you all for your help. Did not see that I was using a globe temp table. Changed code so that I am not using a globe table. Code runs fine. David

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I usually do the following:

    if object_id( '#table' ) is not null
    begin
    drop table #table
    end

  6. #6
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Quote Originally Posted by blindman
    You do not need to test if the temporary table is there. Temporary tables exist only within the scope of the procedure, and are automatically dropped when it completes. (OK, the exception would be global temporary tables, but you probably shouldn't be using those anyway....).

    As far my knowledge says, that the scope of the #TempTable is for the Connection......... Not restricted to the SP only. Hence even if the SP is executed, #TempTable will remain there in the temp DB until and unless the DB connection is lost. With the same DB connection, I can run many SPs one after another, and the #TempTable will remain as it is till either will explicitly drop it or we loose the DB connection.


    Thanks,
    Rahul Jha

  7. #7
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    No Guys, I was wrong......... You were right BlindMan



    Thanks,
    Rahul Jha

  8. #8
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by PMASchmed
    I usually do the following:

    if object_id( '#table' ) is not null
    begin
    drop table #table
    end
    Shouln't this be:
    Code:
    if object_id( 'tempdb..#table' ) is not null
    ...

  9. #9
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by Lexiflex
    Shouln't this be:
    Code:
    if object_id( 'tempdb..#table' ) is not null
    ...

    I believe a table preceded by the '#' will always be assumed to be in tempdb, as user sprocs with 'sp_' are always checked to be in master.

  10. #10
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by DBA_Rahul
    As far my knowledge says, that the scope of the #TempTable is for the Connection......... Not restricted to the SP only. Hence even if the SP is executed, #TempTable will remain there in the temp DB until and unless the DB connection is lost. With the same DB connection, I can run many SPs one after another, and the #TempTable will remain as it is till either will explicitly drop it or we loose the DB connection.


    Thanks,
    Rahul Jha
    Temp table #TempTable will have a name like #TempTable______2344237748 in tempdb..sysobjects.

  11. #11
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by PMASchmed
    I believe a table preceded by the '#' will always be assumed to be in tempdb, as user sprocs with 'sp_' are always checked to be in master.
    True, but unfortunately not for the function OBJECT_ID. If you run the test below you will see that the first statement returns NULL and the second the object id.

    Code:
    CREATE TABLE #tst (c INT)
    
    SELECT OBJECT_ID('#tst')
    UNION
    SELECT OBJECT_ID('tempdb..#tst')
    
    DROP TABLE #tst

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •