How to decode an SQL Server temporary table from other session

Applicable: DBA

Problem

If you are someone like me who wish to have peak on someone else's temporary table it's time to read "Decoding a temporary table from other session in which it is not available for querying".

Solution

First of all we are going to create a temporary table named TempTable61 (#TempTable61) which is on session ID 61.

Okay, now we have a temp table with 3 rows and 3 columns. Next step is the first step of our recovery process, for that we are going to create a new session with some variable and table declarations

As you can see our current session ID is 63. Here we have found the object ID of our target temp table and saving it to our variable @objectID. (Note: Actual name of the temp table in sys.tables must have some postfix values attached to it). Two temp tables we have created in this session is to hold intermediate data of our recovery process. We don't want all the columns in these table but the result of commands we are going to use will have a result which have a format like this. The next step is a DBCC command "DBCC IND". This command will give you page details of the any table whether it is permanent or temporary.

DBCC IND requires 3 parameters. First one is Database name, here our taget table is in tempdb. Second param is object id, 3rd one is index ID , a value less than 1 indicates heap. The result of this command is inserted into one of our temp table created above. The output of command give you something like this

We only need the rows which having a "pagetype" of value 1 which indicating it is a data page.

Now we have the page details of data page, only two fields the "PageFID" and "PagePID" is required which is the file ID and page ID of the target page. Next we are going to give this page details to another DBCC command to extract it's internal data which is "DBCC PAGE" command.

DBCC PAGE requires 3 parameters, 1st one is database name, 2nd one is file ID , 3rd one is page ID, last one indicates informal level of the output. Available options for the last parameters are 0,1,2,3.(Note: DBCC TraceOn enables certain flags, here 3604 enables the engine to give the result to client otherwise DBCC PAGE will not give you any result). The output of the DBCC PAGE will look something like this

In this output we only need the rows whose "ParentObject" an "Object " starts with keyword "Slot". For those rows the Field column represents the field name of target table and VALUE column hold the actual value. Each column goes to a new row. So we need "pivot" to get it in the real structure.

Here we got the field name into a string variable and removed the last ',' with the SUBSTRING(). Then we applied the PIVOT with the previous result and now the result look like this

Hooray! Avoiding the first column now we got the same temp table on session 63 which was on session 61.