Viewing Another Session’s Temporary Table

Introduction

Is it possible to view the contents of a local temporary table, created on a different connection?

Say we run this code on connection 1:

CREATE TABLE #Private
(
    [data] nvarchar(30) NOT NULL
);
GO
INSERT #Private
(
    [data]
)
VALUES
(
    N'Only I can see this'
);

Is there a way to see the contents of the #Private table from connection 2? It isn’t particularly easy, but a user with sysadmin permissions, a bit of internal knowledge (and some patience) can do it.

Bound Sessions?

One suggestion I have heard is to use the (deprecated) Bound Sessions feature. The idea is to get a bind token from sp_getbindtoken and use that to allow a second session to connect using sp_bindsession. The theory is that the now-related sessions will be able to see each other’s local temporary tables.

It sounds plausible, but bound sessions only allow multiple sessions to share the same transaction and locks. Bound sessions do not allow one connection to view another’s temporary tables. Even if it were possible, it might not always be practical to call sp_getbindtoken from the table-owing session.

Reading Data Pages Directly

The winning strategy is to find and read the table’s data pages directly from memory (having been brought in from disk if necessary). We start by finding the temporary table in tempdb:

SELECT
    T.*
FROM tempdb.sys.tables AS T
WHERE
    T.name LIKE N'#Private[_]%';

Armed with the object_id provided by that query, we now need a way to find the physical data pages associated with the table, and then read the row data directly from those pages. Easy!

Finding the Data Pages

The available ways to find the first data page for a table depends on which version of SQL Server you are running.

In SQL Server 2000, we can use the sysindexes system view, which contains a column called first. That view is retained for compatibility in SQL Server 2005 onward but the column has been helpfully renamed to first_page.

In SQL Server 2005 and later versions, we have another, less documented, choice: The sys.system_internals_allocation_units view. This provides the same first_page column as sysindexes. We can join back to sys.tables via the sys.partitions view in the normal way.

This is the full query:

SELECT
    T.[name],
    T.[object_id],
    AU.[type_desc],
    AU.first_page,
    AU.data_pages,
    P.[rows]
FROM tempdb.sys.tables AS T
JOIN tempdb.sys.partitions AS P
    ON P.[object_id] = T.[object_id]
JOIN tempdb.sys.system_internals_allocation_units AS AU
ON  (
        AU.[type_desc] = N'IN_ROW_DATA'
        AND AU.container_id = P.partition_id
    )
    OR
    (
        AU.[type_desc] = N'ROW_OVERFLOW_DATA'
        AND AU.container_id = P.partition_id
    )
    OR
    (
        AU.[type_desc] = N'LOB_DATA' 
        AND AU.container_id = P.hobt_id
    )
WHERE
    T.[name] LIKE N'#Private%';

The join from partition to allocation units depends on the type of page. This is documented in Books Online under the sys.allocation_units entry. See the container_id column description.

Running the above query (on a second connection) produced these results on my system:

Decoding the First Page Pointer

The first_page column contains a pointer to the first data page in binary form. Although the view itself is documented, the format of the pointer is not. The pointer is 6 bytes long, with each byte represented by two hexadecimal characters.

In the sample output above, the twelve hexadecimal characters are: AD 00 00 00 01 00.

Reading from right to left in groups of two, the first two bytes are 00 and 01. These represent the file_id on which the first page is stored: file id #1 in this case.

The remaining four bytes (continuing to read right to left in bytes) are: 00 00 00 AD. This is page #173 in decimal.

-- SQL Server 2019 onward
SELECT 
    [db_id], 
    [file_id], 
    page_id 
FROM sys.fn_PageResCracker(0xAD0000000100); -- first_page value

The output is:

db_id	file_id	page_id
0	1	173

Reading the Data Pages

Many of you will be familiar with the undocumented DBCC PAGE command, which allows anyone with sysadmin permissions to view the contents of an 8KB database page.

Now that we have file and page numbers, we can show the contents directly:

DBCC TRACEON (3604);

DBCC PAGE (tempdb, 1, 173, 3)
  WITH TABLERESULTS;

Trace flag 3604 is required to redirect the DBCC PAGE command output to the client. Output style 3 decodes the raw data into human-readable rows. The WITH TABLERESULTS clause is optional. It affects whether the output appears as printed text or in tabular form.

At the bottom of the query output, we see this:

One other interesting thing from the output is in the page header, where the field m_nextPage points to the next data page for this table.

Thankfully, that pointer is decoded for us by DBCC PAGE and appears in file_id:page_id decimal format. As it is, our test table just has one page, so m_nextPage is 0:0.

Further Reading

Anatomy of a Page by Paul S. Randal.

Acknowledgement

Thanks to David M Maxwell for his #sqlhelp question that prompted this post.