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 (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQLAuthority News – TechEd India 2009 – Day 3 – Product Group Meeting – Final Presentations – Meeting Friends
Next Post
SQL SERVER – Fix : Management Studio Error : Saving Changes in not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created

Related Posts

37 Comments. Leave new

  • I find this works the best.

    IF OBJECT_ID(‘tempdb..#MyTempTable’) IS NOT NULL
    BEGIN
    DROP TABLE #MyTempTable;
    END;

    Reply
  • cool. It works great and solve my query issue. thanks.

    Reply
  • 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.

    Reply
  • Hi 2 everyone…good luck guys…

    Reply
  • sreekanth Chakravarthi
    March 18, 2013 6:09 pm

    hi is there any way to get the source(from which database) the temp table is created.

    Reply
  • Warren LaFrance
    June 20, 2013 3:15 am

    Great stuff…

    Reply
  • 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?

    Reply
  • How to find who dropped the table in sql server 2010?

    Reply
  • 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?

    Reply
    • 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

      Reply
  • –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

    Reply

Leave a Reply

Menu