SQL Tips – Check count of records in Temporary Tables from other sessions | SQL...
source link: https://sqlwithmanoj.com/2015/10/13/sql-tips-check-count-of-records-in-temporary-tables-from-other-sessions/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
SQL Tips – Check count of records in Temporary Tables from other sessions
Recently one Stored Procedure was giving issues on our Production system. The SP had some temporary (#) tables that were getting populated in sequence, but the final result was not as expected. As Temp-Tables cannot be accessed across other sessions you cannot see what’s going on. Also as its a PROD system I could not dissect or change the SP and see what’s happening inside.
Debugging a SP on a DEV machine is simple. I can add some extra checks after temp-table population code to get the record count by using “SELECT @@rowcount”, and was able to see the issues.
But this cannot be done on PROD, as you cannot alter the SPs there. So, to see which temp table is being populated you can use below query. This will also show the records count if any temp table is in mid of population.
SELECTT.NAME AS TABLE_NAME,S.ROW_COUNTFROM TEMPDB.sys.dm_db_partition_stats AS S INNER JOIN TEMPDB.sys.tables AS T ON S.OBJECT_ID = T.OBJECT_ID WHERE S.INDEX_ID < 2and T.NAME like '%#TempTable%'; -- Give #Table name here
Related
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK