THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Page Free Space

See also my SQL Server technical articles on

Viewing Another Session’s Temporary Table

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”:

INSERT #Private
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 (brought in from disk if necessary).  We start by finding the temporary table in tempdb:

FROM tempdb.sys.tables T
WHERE 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 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 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 White
twitter: @SQL_Kiwi

Further Reading

Paul Randal – Anatomy of a Page


My thanks to David M Maxwell (twitter | blog) for his #sqlhelp question that prompted this post.

Published Saturday, August 14, 2010 11:07 AM by Paul White



Fabiano Amorim said:

Hi Paul, some time ago I wrote a post about it,

Take a look I think you will like...  :-)

Tks, keep posting , I love your blog.

August 13, 2010 8:16 PM

Paul White said:


That's awesome - it even translates from Portugese!

I did look, and I did like it :c)

Cool script for viewing all the pages for a table too.


August 13, 2010 9:33 PM

dmmaxwell said:

Wow... I certainly wasn't expecting this.  I will be putting this to immediate use.

Thank you both so much for your time! :-)

August 16, 2010 6:56 AM

Paul White said:

You're welcome, David!

August 16, 2010 9:19 AM

Oliver said:

THIS IS AWESOME!!!!  One of the best real-world examples of using SQL internals!  Using it already - thanks!!!

August 17, 2010 4:35 PM

Paul White said:

Thanks guys!

August 18, 2010 3:04 AM

Rob said:

Is there any way to see the data without SA privilege?  What if I have dbowner on the tempdb?

October 23, 2014 11:00 AM

Paul White said:

Not that I know of, no. Sorry.

October 23, 2014 1:23 PM

Nic Neufeld said:

Thanks Paul, this saved my bacon this morning.  I'd always been able to get rowcounts of temp tables before, which was helpful, but I was troubleshooting a batch process (stuck in an infinite loop) this morning and really needed to see values of the one record inside that table, and thought, well, would DBCC PAGE do the trick?  Quick Google search later and I land here.  Excellent!

And to add two hints to others to help them avoid my mistakes...first, make check the record type to make sure you aren't looking at a ghosted record.  I had 1 record and two pages, and looking at the first page the record didn't make sense until I realized it was ghosted and not a valid record.  So I went to the second page based on the page header, and found that it was an unallocated index page it seemed...took me a bit to realize that the next page was on file id 3.  This being tempdb, most of us have multiple files, so remember to go to the right file id!

Thanks again!

December 9, 2015 2:01 PM

Paul White said:

Thanks Nic, it's good to know that posts from five years ago (gosh!) are still useful from time to time. Thanks also for taking the time to provide such detailed feedback.

December 9, 2015 2:22 PM

Nic Neufeld said:

Also, I adapted your script to find the first page with some of Paul Randal's parsing code to create two columns, one for the file number and one for the page number.  Parsing the binary pointer is one of those things where I would be very liable to introduce human error...

CONVERT(VARCHAR(6),CONVERT(INT,SUBSTRING(AU.first_page,6,1)+SUBSTRING(AU.first_page, 5, 1))) AS FirstPageFile,

CONVERT(VARCHAR(20),CONVERT(INT,SUBSTRING(AU.first_page,4,1)+SUBSTRING(AU.first_page,3,1)+SUBSTRING(AU.first_page,2,1)+SUBSTRING(AU.first_page,1,1))) AS FirstPageNumber

Internals are fun, but especially so when you can solve real problems with them!

December 9, 2015 3:53 PM

Rafael Dontal said:

Hi Paul,

Brilliant post, thanks for that.

I've tried the same script to get data from table variable, couldn't get though. Do you have any idea if it is possible to query table variable on tempdb?

Thanks a million.

January 22, 2016 6:28 AM

PositiveSigner said:

Table Variables' schema are always created in TempDB, but the data will only be written if the table grows too large.  OBJECT_ID() won't work on a table variable. so even if you do find what you think is the right object, the best you can do is get it's schema information.



   UrReallyUniqueColumnName INT,

    id INT,

     val NVARCHAR(10)



   @obj_id =

  FROM tempdb.sys.sysobjects


   sysobjects.xtype = 'u' AND

   EXISTS (SELECT 1 FROM tempdb.sys.syscolumns WHERE = AND = 'UrReallyUniqueColumnName'



   sysobjects.crdate DESC


  FROM tempdb.sys.syscolumns

  WHERE = @obj_id

February 8, 2016 2:51 PM

sanoj ayinikkal vasudevan said:

very nice...I never knew that this would be possible

May 3, 2017 7:26 PM
New Comments to this post are disabled
Privacy Statement