Find All Compressed Tables on SQL Server Database
Super quick script to find all objects that are compressed. This script is grouped by table and compression type as partitioned tables may have different types of compression enabled per partition. If we do not include the “group by"then all partitions will be listed regardless of whether the compression type is different or not, and that looks really messy.
SELECT SCHEMA_NAME(sys.objects.schema_id) AS [Schema]
,OBJECT_NAME(sys.objects.object_id) AS [Object]
,[data_compression_desc] AS [COmpressionType]
FROM sys.partitions
INNER JOIN sys.objects ON sys.partitions.object_id = sys.objects.object_id
WHERE data_compression > 0
AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS'
GROUP BY SCHEMA_NAME(sys.objects.schema_id)
,OBJECT_NAME(sys.objects.object_id)
,[data_compression_desc]
ORDER BY [Schema]
,[Object]
Happy scripting!