SQL SERVER – How to Drop Temp Table – Check Existence of Temp Table

I have received following questions numerous times:

“How to check existence of Temp Table in SQL Server Database?”

“How to drop Temp Table from TempDB?”

“When I try to drop Temp Table I get following error. Msg 2714, Level 16, State 6, Line 4
There is already an object named ‘#temp’ in the database.
How can I fix it?”

“Can we have only one Temp Table or we can have multiple Temp Table?”

“I have SP using Temp Table, when it will run simultaneously, will it overwrite data of temp table?”

In fact I have already answer this question earlier in one of my blog post. I have even explained how Temp Table works in TempDB and how they are managed.

Visit my earlier written article for answers to questions listed above.

SQL SERVER – Fix : Error : Msg 2714, Level 16, State 6 – There is already an object named ‘#temp’ in the database

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

37 thoughts on “SQL SERVER – How to Drop Temp Table – Check Existence of Temp Table

  1. Below is how you can check and delete temp tables.

    IF EXISTS
    (
    SELECT *
    FROM tempdb.dbo.sysobjects
    WHERE ID = OBJECT_ID(N’tempdb..#MyTempTable’)
    )
    BEGIN
    DROP TABLE #MyTempTable
    END

  2. “i tired using it
    i get sysntax error
    Incorrect syntax near ‘)’ ” – SVK

    I suspect that this is because the character ’ (single quote) is invalid, it should be ‘ (apostrophe) instead.

    What I suspect has happened is that either the database for this website is converting apostrophes to single quote or the developer of the query pasted their query into MS Word which then assumed that the character should be a single quote and converted it.

    Solution: Try replacing all single quote characters to apostrophes. I did that and it works fine for me.

  3. Thanks! This was a great place to get a working solution for us. I modified it slightly:
    Select
    1
    FROM
    tempdb.dbo.sysobjects (nolock)
    WHERE
    ID = OBJECT_ID(N’tempdb.dbo.#batch’) and
    [Type] = ‘U’

    Our deployment team doesn’t like “Select *” for any reason, also added a (nolock) hint and limited it User ‘U’ object types only.

  4. Well when I try this I get a NULL value returned.

    select my_id, my_name
    into #tmp_mwj
    from dbo.my_table

    (creates table)

    select OBJECT_ID(‘dbo.#tmp_mwj’)
    (returns null)

    SELECT 1
    FROM dbo.sysobjects
    WHERE ID = OBJECT_ID(‘dbo.#tmp_mwj’)

  5. Here is the Example.
    I have two tables Manager and Employee each has different Empids and Salaries so i want to get the Maximum sal of the Two tables.for that i have used #temptables.

    select salary into #temptbks from Empsal Union select salary from Managers;select max(salary)as salary from #temptbks;
    drop table #temptbks.As soon as temptable is created U should delete so that u can use that same table if the Original table has been Updated.The #temptable will be available in that database itself.In sql-server.If u want this query in asp.net than go for Stored procedure.

  6. Thanks, this worked for me as well. I had been going nuts trying to figure this out.

    IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N’tempdb..#t’)) BEGIN DROP TABLE #t END

  7. Here i really like to make a comment that i m getting an issue with the tempdb as there are some blocking seen while there is a temp table created wthin the procedures there.

    Eg there is a procedure wthin the procedure the are nested procedure called.

  8. Nice Article! as OBJECT_ID() function returns details related to temp table in context of current session so won’t get multiple entries for tables starting with same name, as system suffixes some numbers after local temp table. so it will be tough to identify row related to session in context.

  9. Hi is it good to write many stored procedure and call one with in one instead of writing one single 6 to 15 pages stored procedure.
    I am assuming that sp execute like parallel programming.

    • sivaramakrishnan

      Sorry i am late, But if you code same type or error again then please use this code to solve your error

      SELECT @MYQUERY = ‘IF EXISTS ( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME LIKE ”%##PF’ + CAST ( @@SPID AS VARCHAR ) + ‘%” ) DROP TABLE ##PF’ + CAST ( @@SPID AS VARCHAR )
      EXECUTE ( @MYQUERY )

  10. sivaramakrishnan,

    please use this to solve your error

    SELECT @MYQUERY = ‘IF EXISTS ( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME LIKE ”%##PF’ + CAST ( @@SPID AS VARCHAR ) + ‘%” ) DROP TABLE ##PF’ + CAST ( @@SPID AS VARCHAR )
    EXECUTE ( @MYQUERY )

  11. hi,

    i am facing a problem that is, there are too many temp tables in tempdb. i have already told to my team not to forget dropping it after use. by the way i just want to write a script that “remove ALL temp tables without knowing its name programmatically” and i will schedule it as a nightly job to ensure that the tempdb will not be overgrown. how can i do such a things or is it possible to do?

  12. Hi Sir ,

    IF OBJECT_ID(`tempdb..#temp_cc`) IS NOT NULL
    BEGIN
    DROP TABLE #temp_cc;
    END;

    I have temp caaled temp_cc if executing above query getting invalid sql statement in Toad
    please help.

  13. Pingback: SQL SERVER – Weekly Series – Memory Lane – #029 | SQL Server Journey with SQL Authority

  14. I am getting strange behavior in SQL 2012: I have some lines of code like this:

    IF (object_id(‘tempdb..#procResults’) IS NOT NULL)
    DROP TABLE #procResults;

    CREATE TABLE #procResults
    (…

    It works fine the first time I run the script. Then if I change the script and run it again, it doesn’t drop the existing temp table, nor does it error on the CREATE TABLE statement.

    I believe this is happening because:

    1) if I don’t change the temp table definition, the data that was entered into the temp table the first time is still there after I run the script the second time. Data added the second time is appended.
    2) If I change the temp table definition, I start getting errors that the column I just added doesn’t exist. When I then highlight and execute only the DROP TABLE line of code, the script runs as expected the next time.

    Any ideas why this would happen? Based on 2, I know that the syntax to drop the table is correct, but it doesn’t seem to execute when I re-run a script. Does the DROP TABLE need to be followed by a GO or something in 2012?

  15. i have this case
    SELECT *
    FROM tempdb.dbo.sysobjects
    where name like ‘#%’

    results below, paste in notepad to view.
    name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
    ——————————————————————————————————————————– ———– —– —— —— ———– ————— ———– ———– ———————– ——- ———– —————- —- ——– ——- ——– ———————– ———– ———– ———– ———– ———– ———– ——
    #a__________________________________________________________________________________________________________________00000000000B 173616519 U 1 0 0 0 0 0 2014-05-20 08:01:00.610 0 0 0 U 1 3 0 2014-05-20 08:01:00.610 0 0 0 0 0 0 0
    #14ACD092 346869906 U 1 0 0 0 0 0 2014-05-20 17:16:32.270 0 0 0 U 1 3 0 2014-05-20 17:16:32.270 0 0 0 0 0 0 0
    #a__________________________________________________________________________________________________________________00000000006F 404320737 U 1 0 0 0 0 0 2014-05-21 08:01:00.997 0 0 0 U 1 3 0 2014-05-21 08:01:00.997 0 0 0 0 0 0 0
    #t1_________________________________________________________________________________________________________________00000000000C 441737784 U 1 0 0 0 0 0 2014-05-20 09:27:18.677 0 0 0 U 1 3 0 2014-05-20 09:27:18.677 0 0 0 0 0 0 0
    #2147B1A1 558346657 U 1 0 0 0 0 0 2014-05-20 17:02:59.980 0 0 0 U 1 3 0 2014-05-20 17:02:59.980 0 0 0 0 0 0 0

    Q1) i can’t select those result out. it’s ok, how can i drop those table, can use the “ID” to drop?

    • IF OBJECT_ID(‘temptables’) IS NOT NULL DROP TABLE temptables
      select name into temptables from tempdb.sys.objects where name like ‘#%00000%’

      declare @table varchar(max), @cmd varchar(max)
      while (select count(*) from temptables) > 0
      begin
      set @table = (select top 1 name from temptables)
      set @cmd = ‘IF OBJECT_ID(”tempdb..’ + @table + ”’) IS NOT NULL DROP TABLE ‘ + @table
      exec(@cmd)
      delete from temptables where name = @table
      end
      DROP TABLE temptables

  16. –first drop all temp tables
    declare @cmd varchar(max), @table varchar(max)
    while (select count(*) from tempdb.sys.objects where name like ‘#%00000%’) > 0
    begin
    set @table = (select top 1 name from tempdb.sys.objects where name like ‘#%00000%’)
    set @cmd = ‘IF OBJECT_ID(”tempdb..’ + @table + ”’) IS NOT NULL DROP TABLE ‘ + @table
    exec(@cmd)
    end

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s