Get Table names, row counts, and compression status

For clustered index or heap

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);