Get Table names, row counts, and compression status
When working with SQL Server, understanding the row counts and data compression for your **clustered indexes or heaps** is crucial for performance monitoring and optimization. This SQL query provides a clear overview of this information.
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 -- This filters for clustered indexes (index_id = 1) and heaps (index_id = 0)
AND OBJECT_NAME(p.object_id) NOT LIKE N'sys%' -- Exclude system tables
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);
This query will help you quickly identify tables with large row counts and see what type of data compression is applied to them. It's a handy script to keep in your DBA toolkit!
