Is there a way to get a list of all current temporary tables in SQL Server?

Temp table details

To focus the search on user-created tables and dodge system objects, throw in a type filter:

-- "No system tables allowed" policy
SELECT [name] FROM tempdb.sys.tables WHERE [name] LIKE '#%' AND type = 'U';


Getting nostalgic with SQL Server 2000? Its older sysobjects table has got you covered:

-- An oldie but a goodie
SELECT [name] FROM tempdb..sysobjects WHERE xtype = 'U' AND [name] LIKE '#%';


Play according to the rules, you’d need adequate permissions to query these metadata tables.

Targeted Search

Session-specific versus global temporary tables, how to tell them apart? The cues lie in their naming conventions:

-- "I spy with my little query..."
SELECT [name] FROM tempdb.sys.tables WHERE [name] LIKE '#%'; -- Session-specific tables
SELECT [name] FROM tempdb.sys.tables WHERE [name] LIKE '##%'; -- Global temp tables… they think they own the place


Confirm the existence of an elusive temporary table with the OBJECT_ID function. It's like checking if the bogeyman is under your bed:

-- "Knock Knock. Who's There?"
IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
  PRINT '#MyTempTable exists!';
ELSE
  PRINT 'Guess it got cold feet...';


Tracking Performance

For slow queries involving temporary tables, strap on the SQL Profiler and step into the shoes of a query detective.

As a developer, you might find it beneficial to maintain a timeline of procedural events. A dedicated log table can come in handy:

-- "Dear Diary, today I made a Temp Table..."
CREATE TABLE devOpsLog (timestamp DATETIME, details NVARCHAR(MAX));
INSERT INTO devOpsLog SELECT GETDATE(), 'Created #MyTempTable';


Compatibility Quirks

Uh-oh, the SQL Server version is acting up. What's a coder to do? Adjust. Always adjust.

- SQL Server 2000: Old school `sysobjects` deserves some attention.
- SQL Server 2005 & later: Get jiggy with `sys.tables`.

Mind your syntax and object names. Version changes can be tricky.

##%'; -- Global tents, the bigger crowd SELECT [name] FROM tempdb.sys.tables WHERE [name] LIKE '#%'; -- Local tents, humbler crowd