Instead of checking temporary table existence like this:
IF OBJECT_ID('tempdb..#table') IS NOT NULL
BEGIN;
DROP TABLE #table;
END;
I am using the new DROP IF EXISTS
technique:
DROP TABLE IF EXISTS tempdb..#table;
It is working perfectly, but if the table do not exists, I am getting the following message.
Database name 'tempdb' ignored, referencing object in tempdb.
Does anyone know why and what this message mean?
There is no need to specify tempdb
when talking about #table
- a temporary table is already in tempdb
. I agree that the message is confusing, but it isn't actually an error - it is just a message (PRINT
) telling you that you did something wrong. You actually get that message regardless of whether it exists or not; for example:
-- drop when doesn't exist
drop table if exists tempdb..#foo
go
-- drop when does exist
create table #foo (id int)
drop table if exists tempdb..#foo
outputs the message twice:
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
So: just use:
DROP TABLE IF EXISTS #table;
This is what it wants you to do.