In this post I’ll show you a quick trick to find the rowcount of temporary tables in SQL Server.
Imagine a situation where you are executing a stored procedure that has many SQL queries. In the stored procedure there are few temporary tables. Query is creating temporary tables, inserting rows into them and then performing some actions. While this stored procedure is executing you want to track the progress of the execution. To be more specific you want to know how many rows are inserted into a particular temporary table. Knowing the row count of a temporary table in another sessions seems to be pretty impossible, right? Actually it is very easy. By using below script:
--------------------------------------------------------------------------------------------------------
SELECT T.NAME AS TABLE_NAME
,S.ROW_COUNT
FROM 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 < 2 --------------------------------------------------------------------------------------------------------
Note: To keep it simple I’m assuming you don’t have partitioned tables, anyways it is pointless to create partitions in a temporary table.
By executing above script you can get the row count of all the temporary tables created on the server. In my last post Fastest way to find the row count of a table I mentioned about the system DMV maintained by the SQL Server sys.dm_db_partition_stats. sys.dm_db_partition_stats returns page and row-count information for every partition in the current database. We also know that all the temporary tables are created in the tempdb database. So if we execute the sys.dm_db_partition_stats in the tempdb we can get the row count of the tables in the tembdb database and also the temporary tables created by users.
BUT, there is one thing you should be aware. When we create the temporary table we know that it gets created in the tembdb database but not with the exact same name. SQL Server engine adds some extra (random) characters into the name. Mostly lots of underscores followed by some number. See the below image, I created one temporary table #T and immediately executed above script:
You just need to little smart while giving the name to temporary tables. Otherwise by habit many users can create temporary table with same name (typically #temp or #t) then it will be difficult to know which table is created by which user. So if you give some meaningful names while creating temporary table (e.g. #OrdersLastMonth), above script to know the row_count of temporary tables can really help you.
I hope this script of finding the row count of temporary tables in SQL Server will be useful to you. Do let me know your feedback.
Thanks
Imagine a situation where you are executing a stored procedure that has many SQL queries. In the stored procedure there are few temporary tables. Query is creating temporary tables, inserting rows into them and then performing some actions. While this stored procedure is executing you want to track the progress of the execution. To be more specific you want to know how many rows are inserted into a particular temporary table. Knowing the row count of a temporary table in another sessions seems to be pretty impossible, right? Actually it is very easy. By using below script:
--------------------------------------------------------------------------------------------------------
SELECT T.NAME AS TABLE_NAME
,S.ROW_COUNT
FROM 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 < 2 --------------------------------------------------------------------------------------------------------
Note: To keep it simple I’m assuming you don’t have partitioned tables, anyways it is pointless to create partitions in a temporary table.
By executing above script you can get the row count of all the temporary tables created on the server. In my last post Fastest way to find the row count of a table I mentioned about the system DMV maintained by the SQL Server sys.dm_db_partition_stats. sys.dm_db_partition_stats returns page and row-count information for every partition in the current database. We also know that all the temporary tables are created in the tempdb database. So if we execute the sys.dm_db_partition_stats in the tempdb we can get the row count of the tables in the tembdb database and also the temporary tables created by users.
BUT, there is one thing you should be aware. When we create the temporary table we know that it gets created in the tembdb database but not with the exact same name. SQL Server engine adds some extra (random) characters into the name. Mostly lots of underscores followed by some number. See the below image, I created one temporary table #T and immediately executed above script:
You just need to little smart while giving the name to temporary tables. Otherwise by habit many users can create temporary table with same name (typically #temp or #t) then it will be difficult to know which table is created by which user. So if you give some meaningful names while creating temporary table (e.g. #OrdersLastMonth), above script to know the row_count of temporary tables can really help you.
I hope this script of finding the row count of temporary tables in SQL Server will be useful to you. Do let me know your feedback.
