Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

SQL Server TSQL Stored Procedures which enable you to query table data through the data pages directly. This enables you to see the contents of temp tables created on other threads.

branch: master

Add procedure to get rowcount

This commit implements a new feature which allows you to get the
rowcount of the table. It was
achieved by splitting off the code which gets the object_id in a new
procedure named `sp_select_get_object_id` and by building a new
procedure `sp_select_get_rowcount` which displays
the rowcount based on the `sys.dm_db_partition_stats` dmv
latest commit f41c7b2419
Filip De Vos authored
README.markdown

sp_select and sp_select_get_rowcount

A very hard to crack issue when debugging TSQL Stored procedures on SQL Server 2005, 2008(r2) or 2012 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.

How to use these procedures

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)

Examples

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

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 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 file log.trc.

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

  • Use DBCC IND to return the list of pages to look at with DBCC 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.

Something went wrong with that request. Please try again.