CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2016
gotqn
gotqn

Reputation: 43666

Database name 'tempdb' ignored, referencing object in tempdb

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?

Upvotes: 14

Views: 19022

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1064194

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.

Upvotes: 19

Related Questions