I was recently contacted by a fellow team member who was interested in finding out the number of records in a temporary table which was being used as part of a long-running script.
As I had mentioned in one of my previous posts, local temporary tables are only accessible to the connection that created them. Hence, if a script is already running, the only connection that can execute queries against the local temporary table is the connection itself making this requirement a tricky one to work with.
The Solution
The solution for this is to realize that all tables – permanent or local consume storage either in a system/user database or in the tempdb. Hence, we can access this meta-data to fulfill our requirement. Let’s check it out with a demo.
Demo
In any window, we can access the dynamic management view (DMV): [sys].[dm_db_partition_stats]. As we know, this DMV returns page and row-count information for every partition in the current database.
So, let’s open a new query window in the SQL Server Management Studio and run the following query:
--Create a new test database IF (DB_ID('SQLTwins') IS NULL) BEGIN CREATE DATABASE [SQLTwins]; END GO USE [SQLTwins]; GO --Window 01 BEGIN IF OBJECT_ID('tempdb..#SQLTwinsDemo','U') IS NOT NULL BEGIN DROP TABLE [dbo].[#SQLTwinsDemo]; END CREATE TABLE [dbo].[#SQLTwinsDemo] ([Number] INT NOT NULL, [Value] VARCHAR(50) NOT NULL ); INSERT INTO [dbo].[#SQLTwinsDemo] ([Number], [Value] ) VALUES (9, 'Nine' ), (8, 'Eight'), (7, 'Seven'), (6, 'Six' ), (5, 'Five' ), (4, 'Four' ), (3, 'Three'), (2, 'Two' ), (1, 'One' ); END
Now, in another window, try to run a simple row count query. As expected, it would return an error.
USE [SQLTwins]; GO SELECT COUNT(*) FROM [dbo].[#SQLTwinsDemo]; GO
Msg 208, Level 16, State 0, Line 3 Invalid object name '#SQLTwinsDemo'.
Now, let’s use the DMV: [sys].[dm_db_partition_stats] in another window to get the row count information.
USE [SQLTwins]; GO --Now, do this in Window #2 BEGIN SELECT [st].[name] AS [TableName], [partitionStatistics].[row_count] AS [RowCount] FROM [tempdb].[sys].[dm_db_partition_stats] AS [partitionStatistics] INNER JOIN [tempdb].[sys].[tables] AS [st] ON [st].[object_id] = [partitionStatistics].[object_id] WHERE [st].[name] LIKE '%SQLTwinsDemo%' AND ([partitionStatistics].[index_id] = 0 --Table is a heap OR [partitionStatistics].[index_id] = 1 --Table has a clustered index ) END

Hope it helps!
Until we meet next time,
Be courteous. Drive responsibly,