Get list of compressed objects in database

-- Run the script below on the database which is suspected to have compressed objects to get the details of the objects enabled for data compression:

SELECT

SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]

,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]

,[rows]

,[data_compression_desc]

,[index_id] as [IndexID_on_Table]

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'

ORDER BY SchemaName, ObjectName

--To check for vardecimalstorage format compression run the following command:

SELECT OBJECTPROPERTY(OBJECT_ID(‘<object name(s) from above command output>’),

'TableHasVarDecimalStorageFormat') ;

GO