In SQL Server, you can use any of the following five ways to return a list of temporary tables using Transact-SQL.

These return both local and global temporary tables.

Option 1 – sys.tables

The sys.tables system catalog view is designed specifically for returning table information.

SELECT name
FROM tempdb.sys.tables
WHERE name LIKE '#%';

As with all views on this page, seeing as we want information about temporary tables, we need to query this view in the tempdb database. We can do this either by switching to that database first (so that it’s our current database), or qualifying our query with the database name.

In this example, I qualify the query with the database name.

Option 2 – sys.objects

You can also use the sys.objects system catalog view. If you choose this option, you’ll need to filter it by type so that only user tables are returned.

SELECT name 
FROM tempdb.sys.objects
WHERE type = 'U';

The U stands for “User Table”. An alternative way to do this is to use WHERE type_desc = 'USER_TABLE'.

Option 3 – INFORMATION_SCHEMA.TABLES

If you want an ANSI standard option, use the INFORMATION_SCHEMA.TABLES view. If you choose this option, you’ll also need to filter it by type so that only “base tables” are returned.

SELECT TABLE_NAME
FROM TempDB.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'Base Table';

Option 4 – sp_tables

If you’re looking for a stored procedure option, the sp_tables stored procedure will do the trick.

USE tempdb;
EXEC sp_tables '#%';

You can also explicitly include the argument names.

USE tempdb;
EXEC sp_tables 
  @table_name = '#%',
  @table_owner = 'dbo',
  @table_qualifier = 'tempdb',
  @table_type = "'TABLE'",
  @fUsePattern = 1;

You’ll notice that I switched to tempdb before executing this procedure, even though I supplied the database as one of the arguments. This is actually required. The @table_qualifier argument must match the current database.

Also note that the @fUsePattern pattern must be 1 in this case, because I’m using a wildcard operator in the table name. Setting it to 1 enables pattern matching. Setting it to 0 would disable pattern matching.

Option 5 – dbo.sysobjects

This probably shouldn’t be an option, but I’ll list it anyway. The dbo.sysobjects view is only included in SQL Server for backward compatibility. It has been marked for deprecation and Microsoft advises against using it. If you encounter it in your code, maybe consider changing it to one of the the previous options.

In any case, here’s what it looks like:

SELECT name
FROM tempdb..sysobjects
WHERE name LIKE '#%';

If you’re wondering what the double dot is for (..), it’s a quick way of specifying the default schema. In this case, the default schema is dbo, so we could also write the following:

SELECT name
FROM tempdb.dbo.sysobjects
WHERE name LIKE '#%';