sql-server - 查询所有表数据并进行索引压缩

Question

是否有人碰巧有一个通用 SQL 语句来列出数据库中的所有表和索引，以及每个分区的当前压缩设置？

谢谢。

编辑:这是我尝试查询表时所得到的，但我不确定连接是否正确(我得到了重复项，这似乎是由于索引的存在引起的)

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

我想我应该分享我的最终查询。这将给出两个结果集，第一个是堆和聚集索引的数据压缩，第二个是非聚集索引的索引压缩。

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