Microsoft SQL Server Getting started with Microsoft SQL Server Getting Table Row Count
Example
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'.
