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)