According to this MSDN article http://msdn.microsoft.com/en-us/library/ms190328.aspx
When a temporary table name is specified, the database name must come before the temporary table name, unless the current database is tempdb. For example:
SELECT OBJECT_ID('tempdb..#mytemptable').
But when I am actually creating a temp table in a database other than tempdb the function is not working as specified:
use beta1
create table #t(id int)
select object_name(object_id('tempdb..#t')) as from_beta1
use tempdb
select object_name(object_id('tempdb..#t')) as from_tempdb
drop table #t
This is what I see:
from_beta1
----------
NULL
from_tempdb
---------------
#t_________________...______0000000000B9
What am I missing or doing wrong?
Best Answer
You have to tell
object_namethat it needs to look in the metadata of tempdb.