Reports the temporary database to which a given session is assigned. The input of the tempdb_id function is a server process ID, and its output is the temporary database to which the process is assigned. If you do not provide a server process, tempdb_id reports the dbid of the temporary database assigned to the current process.
tempdb_id()
Finds all the server processes that are assigned to a given temporary database:
select spid from master..sysprocesses
where tempdb_id(spid) = db_id("tempdatabase")
select tempdb_id gives the same result as select @@tempdbid.
