Count rows in every table

Sometimes it's useful to get a row count for every table in a database; for example to get a feel for what's changed in a later version (or to demonstrate that two versions are different), or to get an idea about what's significant when you're inspecting a source system prior to incorporating data into a data warehouse.

See also Count NULLs.

DECLARE @sql VARCHAR(MAX) = ''
 
SELECT @sql += '
  UNION ALL SELECT ''' + s.name + '.' + o.name
  + ''', COUNT(*) FROM [' + s.name + '].[' + o.name + '] WITH (NOLOCK)'
FROM sys.objects o
  INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE o.[type] = 'U'  -- tables only
 
SET @sql = 'WITH cte (
  TableName
, Rows
) AS (
  ' + SUBSTRING(@sql, 15, LEN(@sql)) + '
)
SELECT *
FROM cte
ORDER BY TableName'
 
PRINT @sql
EXEC(@sql)

The [rows] field in sys.partitions provides an approximate number of rows in each partition.

SELECT
  QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.' + QUOTENAME(OBJECT_NAME([object_id]))
, SUM([rows])
FROM sys.partitions
WHERE index_id < 2  -- 0 = heap, 1 = clustered
AND OBJECT_SCHEMA_NAME([object_id]) NOT IN ('sys')
--AND [rows] > 0
GROUP BY [object_id]