====== 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]].

<code tsql>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)
</code>

===== Quick and (possibly) approximate =====
The ''[rows]'' field in ''sys.partitions'' provides an [[https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-partitions-transact-sql|approximate number of rows in each partition]]. 

<code tsql>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]
</code>

{{tag> tsql dynamic testing warehouse}}