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);
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.
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 (brought in from disk if necessary). We start by finding the temporary table in tempdb:
FROM tempdb.sys.tables 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, which 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:
FROM tempdb.sys.tables T
JOIN tempdb.sys.partitions P
ON P.[object_id] = T.[object_id]
JOIN tempdb.sys.system_internals_allocation_units 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 six bytes long, with each byte is 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. The remaining four bytes (continuing to read right to left in bytes) are: 00 00 00 AD. This is page #173 in decimal.
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’s output to the client window. Output style 3 decodes the raw data into human-readable rows. The WITH TABLERESULTS clause is optional, just affecting whether the output appears as printed text or in the familiar tabular form. At the bottom of the 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).
Paul Randal – Anatomy of a Page
My thanks to David M Maxwell (twitter | blog) for his #sqlhelp question that prompted this post.