11

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?

gotqn
  • 42,737
  • 46
  • 157
  • 243

1 Answers1

16

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.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • Very nice, we do not need to point to the `tempdb` anymore as in the `OBJECT_ID` function. – gotqn Jan 12 '18 at 09:28
  • `DROP TABLE IF EXISTS #table;` it throws error. **Incorrect syntax near the keyword 'IF'.** – DineshDB Jan 12 '18 at 09:28
  • @gotqn indeed; it is only `OBJECT_ID` that needs that, and we aren't using `OBJECT_ID` here – Marc Gravell Jan 12 '18 at 09:29
  • @DineshDB on what server version? I've literally *just run it* (on 2016) - it works fine – Marc Gravell Jan 12 '18 at 09:29
  • @DineshDB `drop if exists` can be used with `SQL Server 2016` - https://www.google.bg/search?q=tsql+drop+if+exists&oq=tsql+drop+if+exists&aqs=chrome..69i57j0l5.4666j0j7&sourceid=chrome&ie=UTF-8 – gotqn Jan 12 '18 at 09:29
  • Oh sorry @MarcGravell, I'm using 2014 only – DineshDB Jan 12 '18 at 09:30
  • 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 .dbo.tableXpto But later on I decided to use a temporary table and I did this: select * from .dbo.#Temp And that message started to appear. I just had to do this: select * from #Temp No need to have ".dbo." previous to the temporary table. – RodrigoCampos Nov 06 '19 at 11:18