SQL Server Finding Row Counts of Temp Tables

The other day we had a large update script (Several hours) against on of our production DB’s. The script populated a session temp table, then joined the temp table to a second table to update. It performed this in a loop batch of 25K. At the end of each batch it deleted matching records (25K) from temp table. After kicking it off I realized there were no status statements (Raiserror) to view progress of script. Normally I’d write some T-SQL statement to monitor the status of the source table, but since it was a session based temp table, I couldn’t access it. In order to get at this info I queried the sysindexes system table of tempdb to get at row counts. Here’s a similar query I ran.


select rows from sysindexes i
inner join sys.tables t
on i.id = t.object_id
where t.name like '#temptable%'
and indid in (1,0)