CODEWITHSUNDEEP
sql-serversql-server-2005sql-server-2000temp-tables
MatthewMartin
MatthewMartin

Reputation: 33143

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

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.

Upvotes: 0

Views: 321

Answers (2)

gbn
gbn

Reputation: 432210

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.

Upvotes: 1

Brandon
Brandon

Reputation: 69973

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

Upvotes: 4

Related Questions