Get List of Local and Global SQL Server Temp Tables

By: Rick Dobson

Overview

Both local and global temporary tables are stored in the tempdb database.  In this section we will show how you can see what temp tables currently exist.

Listing Local and Global SQL Server Temporary Tables

As an administrator or even an advanced user, you may sometimes find it useful to list all the global and temporary tables in the tempdb database.  Anyone can query objects in tempdb (unless permission is explicitly revoked, which is not recommended).  The set of all tables in tempdb can be obtained by referencing tempdb.sys.tables in the FROM clause of a SELECT statement.  The tables in tempdb can be more than just global and local temp tables.  Therefore, you need to limit the result set with a WHERE clause to list just global temp tables or local temp tables.

The following script provides the syntax for listing just global temp tables or local temp tables.  The filter for listing global temp tables is to find all tempdb tables beginning with ##.  A two-part filter is required for listing local temp tables.  The local temp table names must begin with #, but not begin with ##.

-- list global temp tables
SELECT
  name,
  type,
  type_desc,
  create_date
FROM tempdb.sys.tables
WHERE name LIKE '##%'

-- list only local temp tables
SELECT
  name,
  type,
  type_desc,
  create_date
FROM tempdb.sys.tables
WHERE name LIKE '#%'
AND name NOT LIKE '##%'

The following display shows the result sets from the preceding two SELECT statements.  The top result set is for global temp tables with names of ##not_married and ##married.  The bottom result set is for local temp tables with names of #married_female, #married_male, and #not_married.

SQL Server allows the same local temp table name to be used for different local temp tables – so long as the tables are from different connections.  If this happens, then the beginning part of both local temp tables will have the same name.  However, the trailing part of the name will be distinct for each local temp table with the same beginning part.  SQL Server tracks the different local temp tables for each connection based on the trailing part of the name.  In this way, SQL Server can use the proper source local temp table from two different connections where each local temp table may have different data from its connection.