Query all table data and index compression

Question

Does anyone happen to have a generic SQL statement that'll list all of the tables and indexes in a database, along with their current compression setting, for each partition?

Thanks.

EDIT: This is as far as I got in my attempt to query tables, but I'm not sure the join is correct (I'm getting duplicates, which seems to be caused by the presence of indexes)

SELECT [t].[name], [p].[partition_number], [p].[data_compression_desc] FROM [sys].[partitions] AS [p] INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id] 

Answer 1

I thought I'd share my final query. This'll give two result sets, the first of data compression for heaps and clustered indexes, and the second of index compression for non-clustered indexes.

SELECT [t].[name] AS [Table], [p].[partition_number] AS [Partition],     [p].[data_compression_desc] AS [Compression] FROM [sys].[partitions] AS [p] INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id] WHERE [p].[index_id] in (0,1)  SELECT [t].[name] AS [Table], [i].[name] AS [Index],       [p].[partition_number] AS [Partition],     [p].[data_compression_desc] AS [Compression] FROM [sys].[partitions] AS [p] INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id] INNER JOIN sys.indexes AS [i] ON [i].[object_id] = [p].[object_id] AND [i].[index_id] = [p].[index_id] WHERE [p].[index_id] > 1