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,
SQLTwins by Nakul Vachhrajani 