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)
37 Comments. Leave new
I find this works the best.
IF OBJECT_ID(‘tempdb..#MyTempTable’) IS NOT NULL
BEGIN
DROP TABLE #MyTempTable;
END;
cool. It works great and solve my query issue. thanks.
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.
Try:
IF OBJECT_ID(N’tempdb..#temp_cc’) IS NOT NULL
BEGIN
DROP TABLE #temp_cc;
END;
Hi 2 everyone…good luck guys…
hi is there any way to get the source(from which database) the temp table is created.
Great stuff…
Hi..!
Drop Table #temptable
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?
How to find who dropped the table in sql server 2010?
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
–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
oops please delete this one – loops endlessly if others create temp tables