A very hard to crack issue when debugging TSQL Stored procedures on SQL Server from the earliest
versions all the way to SQL Server 2016 is the fact that you can not see the contents of temp
tables outside the session where they are created. This problem is now solved thanks the procedures
sp_select and sp_selectpages. This code would not have been possible without the blog posts
from Fabiano Neves and Jonathan Kehayias.
The procedures can be deployed on the master database with the included rakefile (or just manually open them in SQL Server Management Studio and run them on master)
Once they are deployed you can call the procedure sp_select from any database. The procedure
sp_select accepts the following parameters:
- @table_name: This is the fully qualified table name to display the contents from. (for example
msdb.dbo.MSdbms) - @spid: this optional parameter can be used to specify a spid on which the temp table is created. (useful on busy servers)
- @max_pages: this optional parameter is used to limit the amount of data returned. (default 1000)
To get the rowcount of the table you can run the procedures sp_select_get_rowcount from any
database. The procedure sp_select_get_rowcount accepts the following parameters
- @table_name: This is the fully qualified table name to display the contents from. (for example
msdb.dbo.MSdbms) - @spid: this optional parameter can be used to specify a spid on which the temp table is created. (useful on busy servers)
Run the following code in one query window:
CREATE TABLE #temp (id int, name varchar(200))
INSERT INTO #temp VALUES (1, 'Filip')
INSERT INTO #temp VALUES (2, 'Sam')
Now open a second query and run the following statement:
exec sp_select 'tempdb..#temp'
The result will be
| id | name |
|---|---|
| 1 | Filip |
| 2 | Sam |
When you want to see the rowcount you run
exec sp_select_get_rowcount 'tempdb..#temp'
The result will be
| rows |
|---|
| 2 |
The procedure sp_select will try to pinpoint the object_id of the table you are trying to get
the data from by calling sp_select_get_object_id. 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
- There is only 1 temp table with the name you are looking for. ==> get the object_id
from
tempdb.sys.tables - There are more than 1 temp table with the name you are looking for and you did not specify
a
@spid. ==> find the first temp table matching the database name of the database you are running the procedure on - There are more than 1 temp table with the name you are looking for and you did specify a
@spid==> match the temp table with the spid by mining the default trace filelog.trc.
Once the object_id is determined the procedure sp_selectpages will be used to return the content.
- Use
DBCC INDto return the list of pages to look at withDBCC PAGE - Loop over all the pages and store the page content with
DBCC PAGE - use the PIVOT statement to pivot the key/value results to the original table layout
Note: All the fields in the resultset will have the type VARCHAR(6000)
The procedure sp_select_get_rowcount will use the same system to pinpoint the object_id and will
query the sys.dm_db_partition_stats dmv to get the rowcount. Do note that this view depends on the
statistics on the table, so the rowcount can be off a bit. If you suspect this is the case, you can
run DBCC UPDATEUSAGE (myDatabaseName,"mySchema.myTable"); to recalculate the rowcounts.