Skip to content

View Tables and Indexes with Compression

Queries that list every table and index partition with its data compression setting.

Published Mar 5, 2009

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
An unhandled error has occurred. Reload 🗙