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.