How to tell what temporary tables are currently in scope in SQL Server?

Question

I often get the errors:

Msg 208, Level 16, State 0, Line 1
Invalid object name '#foo'.

Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#foo', because it does not exist in the system catalog.

How do I know what temporary tables there are in scope? They obviosly don't show up in SSMS like base tables do.

Answer 1

You can check to see if the table exists before trying to perform a query on it.

IF object_id('tempdb..#foo') IS NOT NULL

Answer 2

Expanding Brandon's answer...

In SSMS query window 1:

CREATE TABLE #foo (bar int)
GO
CREATE TABLE ##bar (foo int)
GO
SELECT object_id('tempdb..#foo'), object_id('tempdb..##bar')
GO

In window 2:

SELECT object_id('tempdb..#foo'), object_id('tempdb..##bar')

##bar is visible in both sessions, as expected. #foo in the local session only.