Count All Rows In All Tables

There are a few ways to count all of the rows in all of your tables in a database.

The “Fastest Option” actually pulls the information from the database where the numbers are stored rather than taking time to count rows.

SELECT t.NAME AS table_name
	,i.rows
FROM sys.tables AS t
INNER JOIN sys.sysindexes AS i ON t.object_id = i.id
	AND i.indid < 2
ORDER BY table_name

--OR
SELECT @@ServerName AS SERVER
	,DB_NAME() AS DBName
	,OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName
	,OBJECT_NAME(p.object_id) AS TableName
	,i.Type_Desc
	,i.NAME AS IndexUsedForCounts
	,SUM(p.Rows) AS Rows
FROM sys.partitions p
JOIN sys.indexes i ON i.object_id = p.object_id
	AND i.index_id = p.index_id
WHERE i.type_desc IN (
		'CLUSTERED'
		,'HEAP'
		)
	-- This is key (1 index per table)
	AND OBJECT_SCHEMA_NAME(p.object_id) <> 'sys'
GROUP BY p.object_id
	,i.type_desc
	,i.NAME
ORDER BY SchemaName
	,TableName;