SELECT DISTINCT SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(o.object_id) AS TableName, i.name AS IndexName, p.data_compression_desc AS CompressionType, i.type_desc AS StorageType FROM sys.partitions p INNER JOIN sys.objects o ON p.object_id = o.object_id JOIN sys.indexes i ON p.object_id = i.object_id AND i.index_id = p.index_id WHERE p.data_compression > 0 AND SCHEMA_NAME(o.schema_id) <> 'SYS'
