View Tables and Indexes with Compression
Queries that list every table and index partition with its data compression setting.
What is actually compressed in this database? Two quick queries: the first lists every table partition (heap and clustered) with its compression setting, and the second lists every index on every partition with its name and compression. A fast audit before or after a compression project.
SELECT [t].[name] AS [Table], [p].[partition_number] AS [Partition],
[p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p] (nolock)
INNER JOIN sys.tables AS [t] (nolock) 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],[p].[index_id]
FROM [sys].[partitions] AS [p] (nolock)
INNER JOIN sys.tables AS [t] (nolock) ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.indexes AS [i] (nolock) ON [i].[object_id] = [p].[object_id] AND [i].[index_id] = [p].[index_id]
WHERE [p].[index_id] is not null