none
How do I check if #tempTable exists?

    Question

  • Is there a way in T-SQL  to check to see if a #tempTable exists?  I want to write a proc the uses a temp table, but I first need to see if the table already exists.  if it does I want to drop it, otherwise skip
    Thursday, May 17, 2007 10:34 PM

Answers

  • There is no reason to check for #tempTable if you are creating the temporary table within a SP and using it there. Local temporary tables (#something) are scoped to the batch / session / module. SQL Server 2000 onwards allows you to reference say a temporary table created in a SP inside a trigger or other SPs called from the main one. Global temporary tables are instance specific so you will have to serialize creation/deletion etc. In any case, you can use OBJECT_ID function to check for temporary tables. Specify the temporary table using 3-part name like:
     
    if OBJECT_ID('tempdb..#test') is not null
     --- temp table exists
    Friday, May 18, 2007 12:43 AM
  • Try:

     

    if object_id('tempdb..#t1') is not null

        drop table #t1

     

     

    AMB

    Friday, May 18, 2007 12:14 AM
    Moderator

All replies

  • The life of a #temp table is very short.

     

    If the #temp table is not in your stored procedure, it doesn't exist. It is dropped when the procedure it resides in completes.

     

    Adamus

    Thursday, May 17, 2007 10:58 PM
  • I realize that it is short lived.  Is each call from an ASP page considered a different life, i.e.  if I use a postback on my page that uses a #temp table, will that temp table be dropped after EACH postback?
    Thursday, May 17, 2007 11:20 PM
  • Code Snippet

    if exists (
        select  * from tempdb.dbo.sysobjects o
        where o.xtype in ('U') 

       and o.id = object_id(N'tempdb..#tempTable')
    )
    DROP TABLE #tempTable;

    Thursday, May 17, 2007 11:23 PM
  • A local temp table lives for the life of a connection.
    A global temp table lives for the life of all connections referencing it.
    Thursday, May 17, 2007 11:25 PM
  • Try:

     

    if object_id('tempdb..#t1') is not null

        drop table #t1

     

     

    AMB

    Friday, May 18, 2007 12:14 AM
    Moderator
  • There is no reason to check for #tempTable if you are creating the temporary table within a SP and using it there. Local temporary tables (#something) are scoped to the batch / session / module. SQL Server 2000 onwards allows you to reference say a temporary table created in a SP inside a trigger or other SPs called from the main one. Global temporary tables are instance specific so you will have to serialize creation/deletion etc. In any case, you can use OBJECT_ID function to check for temporary tables. Specify the temporary table using 3-part name like:
     
    if OBJECT_ID('tempdb..#test') is not null
     --- temp table exists
    Friday, May 18, 2007 12:43 AM
  •  

    Remember that temporary tables are specific to a user, so #test does not exist in tempdb.  It will be named something like #test_____________________. 

    So select object_id('tempdb..#test') will fail.  You will need to use something like:

     

    SELECT [name] FROM tempdb.dbo.sysobjects WHERE xtype = 'U' and [name] like '#test%'

     

    In SQL 2005 you should also be using the new schemas which are much easier to understand:

    SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#test%'

     

    And in SQL2005 there's an easier way without having to resort to tempdb at all.  Use the Try..Catch block.  In my example, I've put a message in the Catch block, but if you leave it blank the statement will not fail if the table doesn't exist.  It would if you tried a plain DROP TABLE #test on a non-existent table.

     

    BEGIN TRY

      DROP TABLE #test

    END TRY

    BEGIN CATCH

      PRINT 'does not exist'

    END CATCH

    Sunday, March 30, 2008 2:14 PM
  •  

    object_id('tempdb..#test') is working fine ,actually the search in tempdb..systables(or sysobjects) might get into wrong results because it might find a temporary table for another connection
    • Proposed as answer by JamesB3939 Tuesday, March 25, 2014 5:57 PM
    Thursday, August 21, 2008 1:56 PM
  • How to get the 'real' name of the table?

    I have a process that temporarily stores the data in the tempdb, while restructuring the 'real' table.

    I have a program that backups, then stores the data into the tempdb, then restructures tables.

    If, for example, there is a problem between the dropping of the existing table, and rebulding it, can I retrieve the data that is till in the tempdb table?

     

     

    I'd like to see if the table is in there, and if so, I would like to retrieve the data.

    running this :

    Select * from tempdb..dbo.#11DF9047

    reports :

    Msg 208, Level 16, State 0, Line 1

    Invalid object name '#11DF9047'.

     

    Tuesday, August 26, 2008 7:44 PM
  • Using Wilson32's Try..Catch block worked best for me. I recommend it.


    Dominik
    Tuesday, July 07, 2009 3:36 PM
  • Agreed.  Best Answer.
    Monday, August 31, 2009 10:58 PM
  • Hi Wislon32...

    Your TRY..CATCH approach will work, but I have to disagree regarding your other comments.

    1) Go into SSMS and open a new query window and CREATE a table called #test
    2) Open a second query window and, again, CREATE a TABLE called #testnumber2
    3) Open a third query window and this time CREATE a TABLE called #testnumber3

    Now you have three connections... each of them created temp tables that started with '#test'.

    Now in each of the query windows, try the SELECT statements you suggested using:

    SELECT [name] FROM tempdb.dbo.sysobjects WHERE xtype='U' and [name] like '#test%'

    or...

    SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#test%'

    Each of those will give you a result of 3 rows.  So each connection THINKS that a table exists called #test.  But a table called #test really only exists IN THE FIRST QUERY WINDOW ONLY.

    Now go into each of the 3 query windows and type SELECT OBJECT_ID('tempdb..#test').  This is the suggestion that Alejandro (Hunchback) suggested.

    Note that ONLY THE FIRST QUERY WINDOW returned a NON-NULL value.  That's because it was the ONLY query window that actually created a tabled called #test.

    Thus, Alejandro's (Hunchback's) suggestion is the correct approach.  It will NOT fail as you suggested.

    This is why the suggested approach for conditionally dropping a temp table is this:

    IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test
    --Brad (My Blog)
    Monday, August 31, 2009 11:43 PM
    Moderator
  • There are valid reasons why you want to check if a temp table exists. For example, there are situations where you want to pass information into a trigger other than the inserted and deleted tables. Since you can't pass variables, your only recourse is to create a temp table, set some values in it and then access it from the trigger. In this situation, you might want to check the existence of the temp table before actually accessing it.

    Thursday, February 04, 2010 6:22 PM
  • You can also check temp table existence this by following sql stmt:

    SELECT TABLE_NAME,*
    FROM tempdb.INFORMATION_SCHEMA.TABLES

    Tuesday, July 20, 2010 12:43 PM
  • You can also check temp table existence this by following sql stmt:

    SELECT TABLE_NAME,*
    FROM tempdb.INFORMATION_SCHEMA.TABLES


    Again, though, that will give you ALL of the temp tables.

    1) In a query window, create a temp table called #TEST

    2) In a second query window, create a temp table called #TESTNUMBER2

    3) Now do your query.  You'll see a table called #TEST____________________________00000000009 (or something like that) and a table called #TESTNUMBER2________________________0000000000A (or something like that).

    4) So how do you check for the existence of a temp table called #TEST?  You can't do a query on TEMPDB.INFORMATION_SCHEMA.TABLES looking for the existence of a TABLE_NAME LIKE '#TEST%', because you'd pick up both tables.

    Again, the best sure-fire way to do it is to just check for OBJECT_ID('TEMPDB..#TEST')... if it's NOT NULL, then the temp table exists.

     


    --Brad (My Blog)
    Tuesday, July 20, 2010 4:34 PM
    Moderator
  • thanks for the code:  I had to add Begin and End to get it to work.

    Code Snippet
    if exists (
    select * from tempdb.dbo.sysobjects o
    where o.xtype in ('U')
    and o.id = object_id(N'tempdb..#tempTable')
    )
    BEGIN
    DROP TABLE #tempTable;
    END
    
    

    Marc Noon

    Thursday, July 28, 2011 8:16 PM
  • Just simple to write below code

    Create Procedure [dbo].[usp_proc]
    as
    Begin
    if object_id('tempdb..#temp') is not null drop table #temp
    --------------
    ------------
    write code for your bussiness logic
    ------------
    ------------

    end

    Thursday, June 20, 2013 4:36 PM
  • You have to be careful when being in the mindset of “There is no reason to check for #tempTable if you are creating the temporary table within a SP and using it there”. I recently went through a scenario where an SP created a temp table went on to do some processing and then failed for reasons unrelated to the temp table. However, once the issue was resolved and the SP was once again executed it failed because the temp table it was trying to create already existed, it never dropped it. So moving forward I always check to see if my temp table exists. It’s just good practice, it doesn’t hurt, and it’s just one line of code.


    Joe Stariha | Magenic – North Region | magenic.com

    Tuesday, July 23, 2013 4:13 PM
  • Then you can expleing why I'm receiving the error:

    [Microsoft][ODBC SAL Server Driver][SQL Server]There is already an object named '#Dare' in then database. (#2714)

    the second time I run my passtrought query that create a new temp table

    Bye


    Davide

    Thursday, January 02, 2014 4:04 PM
  • Is there a way in T-SQL  to check to see if a #tempTable exists?  I want to write a proc the uses a temp table, but I first need to see if the table already exists.  if it does I want to drop it, otherwise skip

    So many things wrong. In T-SQL dialect, that 1970's # tells the original one-pass compiler to scope this table for the session in which the procedure is declared. It will not live long :)

    But more than that, we do not use them any more. We have CTEs, derived tables, and other ANSI/ISO Standard SQL constructs. They will optimize and port. Your temp table is how punch card and mag tape programmers fake magnetic scratch tapes.

    Post the proc and let's see if we can fix up what you are doing. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, January 02, 2014 9:12 PM
  • Because you didn't create the temp table in a stored procedure, and ended up creating a session-scoped temp table that will exist until you drop it or your session ends.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Thursday, January 02, 2014 9:16 PM