tempdb syntax - what is wrong 1

[thamms]

Hi,

I have a question as to why this syntax is wrong:

IF OBJECT_ID('tempdb..##Columns') IS NOT NULL
DROP TABLE tempdb..##Columns

Error:
Database name 'tempdb' ignored, referencing object in tempdb.

But this is right:

IF OBJECT_ID('tempdb..##Columns') IS NOT NULL
DROP TABLE ##Columns -- can't reference tempdb?

Seems that one should reference the "tempdb" qualifier when dropping the table?

Thanks

 

[SQLSister]

The way that temp db works it doesn't create an actual table in the db with exactly that name (because two people could run the same proc at the same time and create the same table with the same name and temp db needs to know which one belongs to which connection). It knows that if you prefix the tablename with # or ## that the table is in #tempdb.

"NOTHING is more important in a database than integrity." ESquared