Database name 'tempdb' ignored, referencing object in tempdb

Question

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?

Solution

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.

Comments

[gotqn]

Very nice, we do not need to point to the tempdb anymore as in the OBJECT_ID function.

[DineshDB]

DROP TABLE IF EXISTS #table; it throws error. Incorrect syntax near the keyword 'IF'.

[Marc Gravell]

@gotqn indeed; it is only OBJECT_ID that needs that, and we aren't using OBJECT_ID here

[Marc Gravell]

@DineshDB on what server version? I've literally just run it (on 2016) - it works fine

[gotqn]

@DineshDB drop if exists can be used with SQL Server 2016 - google.bg/…

[DineshDB]

Oh sorry @MarcGravell, I'm using 2014 only

[RodrigoCampos]

What happen with me and it could help others was, I had a query where I specified the full path of the table,i.e., for instance: select * from <database>.dbo.tableXpto But later on I decided to use a temporary table and I did this: select * from <database>.dbo.#Temp And that message started to appear. I just had to do this: select * from #Temp No need to have "<database>.dbo." previous to the temporary table.