FoxTricks

Query the content of a temp table from another connection in SQL Server

The TSQL Debugger in SQL Server has become more mature over time (after a little dip in SQL Server 2005) and it is now quite easy to debug stored procedures and functions. The one feature still missing though is the lack of a view on the content of temp tables while stepping through the code.

Some older ways of viewing the content is by peppering the code with select statements, or by replacing the temp table by a permanent table.

I have long dreamed of a way to query the content of a temp table from another connection. This way I can step through a Stored procedure, and check the content of the table while the procedure is waiting at a breakpoint. Since I never managed to find a proper solution I decided to build something on my own.

This code would not have been possible without the blog posts from Fabiano Neves and Jonathan Kehayias.

The result of this work is the “sp_select” project on github (https://github.com/FilipDeVos/sp_select).

This project contains 2 stored procedures:

They will use the output of the “DBCC PAGE” command to build up the content of a temp table.

Usage

The procedures can be used as follows. First open a new Query window and run the following statement without closing the query window:

Open a second query window and run the following statement to see the content of the temp table:

This query will return the following result set:

IdName
1 Filip
2 Sam

How does it work

 

The procedure sp_select will try to pinpoint the object_id of the table you are trying to get the data from. When specifying a permanent table this is quite easy the function object_id() will return the correct value. When the target is a temp table this is quite difficult as SQL Server does not store a link between the temp table in tempdb and the session in an easily accessible way.

There are 3 scenarios implemented in the procedures.

Once the object_id is determined the procedure sp_selectpages will be used to return the content.

Known issues

There are a few outstanding issues to fix, but they will only extend the use of this procedure.