Compressed Tables List

SELECT
	[t].[name] [TableName],
	[i1].[name] [IndexName],
	[i1].[type_desc] [IndexType],
	[fg].[name] AS [FileGroupName],
	[p].[partition_number] AS [PartitionNumber],
	CAST([p].[rows] AS FLOAT) AS [RowCount],
	[p].[data_compression] AS [DataCompression],
	[p].[data_compression_desc] [DataCompressionDesc]
FROM [sys].[tables] AS [t]
	INNER JOIN [sys].[indexes] AS [i1] ON [i1].[object_id] = [t].[object_id]
	INNER JOIN [sys].[partitions] AS [p] ON [p].[object_id]=CAST([t].[object_id] AS INT) AND [p].[index_id]=[i1].[index_id]
	INNER JOIN [sys].[indexes] AS [i2] ON [p].[object_id] = [i2].[object_id] AND [p].[index_id] = [i2].[index_id]
	LEFT OUTER JOIN [sys].[destination_data_spaces] AS [dds] ON [dds].[partition_scheme_id] = [i2].[data_space_id] AND [dds].[destination_id] = [p].[partition_number]
	LEFT OUTER JOIN [sys].[partition_schemes] AS [ps] ON [ps].[data_space_id] = [i2].[data_space_id]
	LEFT OUTER JOIN [sys].[partition_range_values] AS [prv] ON [prv].[boundary_id] = [p].[partition_number] AND [prv].[function_id] = [ps].[function_id]
	LEFT OUTER JOIN [sys].[filegroups] AS [fg] ON  [fg].[data_space_id] = [dds].[data_space_id] OR [fg].[data_space_id] = [i2].[data_space_id]
WHERE [p].[data_compression] <> 0
ORDER BY [TableName], [IndexName], [PartitionNumber] ASC
GO