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]
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