The following example can be used to find the total row count for a specific table in a database if
table_name is replaced by the the table you wish to query:
SELECT COUNT(*) AS [TotalRowCount] FROM table_name;
It is also possible to get the row count for all tables by joining back to the table's partition based off the tables' HEAP (index_id = 0) or cluster clustered index (index_id = 1) using the following script:
SELECT [Tables].name AS [TableName], SUM( [Partitions].[rows] ) AS [TotalRowCount] FROM sys.tables AS [Tables] JOIN sys.partitions AS [Partitions] ON [Tables].[object_id] = [Partitions].[object_id] AND [Partitions].index_id IN ( 0, 1 ) --WHERE [Tables].name = N'table name' /* uncomment to look for a specific table */ GROUP BY [Tables].name;
This is possible as every table is essentially a single partition table, unless extra partitions are added to it. This script also has the benefit of not interfering with read/write operations to the tables rows'.