Script to Check Row Count for All Tables in SQL Server

  • sys.partitions and sys.objects
  • Consider ONLY Heap or Clustered Index that gives you the count
SELECT SCHEMA_NAME(o.Schema_ID) AS [Schema Name], OBJECT_NAME(p.object_id) AS [ObjectName]
	, SUM(p.Rows) AS [RowCount], p.data_compression_desc AS [Compression Type]
FROM sys.partitions AS p WITH (NOLOCK)
INNER JOIN sys.objects AS o WITH (NOLOCK)
	ON p.object_id = o.object_id
WHERE index_id < 2 --ignore the partitions from the non-clustered index if any
	AND OBJECT_NAME(p.object_id) NOT LIKE N'sys%'
	AND OBJECT_NAME(p.object_id) NOT LIKE N'spt_%'
	AND OBJECT_NAME(p.object_id) NOT LIKE N'queue_%' 
	AND OBJECT_NAME(p.object_id) NOT LIKE N'filestream_tombstone%' 
	AND OBJECT_NAME(p.object_id) NOT LIKE N'fulltext%'
	AND OBJECT_NAME(p.object_id) NOT LIKE N'ifts_comp_fragment%'
	AND OBJECT_NAME(p.object_id) NOT LIKE N'filetable_updates%'
	AND OBJECT_NAME(p.object_id) NOT LIKE N'xml_index_nodes%'
	AND OBJECT_NAME(p.object_id) NOT LIKE N'sqlagent_job%'
	AND OBJECT_NAME(p.object_id) NOT LIKE N'plan_persist%'
GROUP BY  SCHEMA_NAME(o.Schema_ID), p.object_id, data_compression_desc
ORDER BY SUM(p.Rows) DESC 
OPTION (RECOMPILE)

Thanks to Glenn Berry for sharing the script.