在不使用 SP 或臨時表的情況下查詢所有數據庫上的索引碎片

我正在嘗試在多伺服器環境中編譯所有數據庫的索引碎片報告。查詢將通過 openquery 命令執行，所以我想讓它盡可能簡單，不使用儲存過程、臨時表等。

我知道以下可以恢復所有數據庫上的所有索引

select * from sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'SAMPLED')

但是一旦我們將它加入到 sys.indexes、sys.objects 中，我們就會限制系統範圍的索引，而是專注於目前正在執行的數據庫中的索引。

有沒有辦法解決這個問題，所以我可以獲得所有數據庫的以下輸出：

數據庫名稱、索引名稱、索引類型、頁數、碎片百分比。

不使用臨時表是不可能實現的，因為我們使用的 DMV/DMF 是數據庫範圍的，要求是伺服器範圍的，我看不出使用臨時表有任何困難或複雜性，如下所示：

Declare @Tbl table (ServerName varchar(128), DBName varchar(128), SchemaName varchar(128), TableName varchar (100), IndexName varchar (100), FragPercent float, IndexType tinyint, IsPrimaryKey bit);

Insert into @Tbl
exec SP_MSforeachdb @command1 = 
           'use [?];
               select  @@Servername, 
                       DB_NAME(),
                       sc.name as SchemaName,
                       object_name (s.object_id) as TableName, 
                       I.name, 
                       s.avg_fragmentation_in_percent, 
                       I.type, 
                       I.is_primary_key
               from sys.dm_db_index_physical_stats (DB_ID (), NULL, NULL, NULL, ''LIMITED'') as S
                   join sys.indexes as I on s.object_id = I.object_id and s.index_id = I.index_id
                   join sys.objects as O on s.object_id = O.object_id 
                   join sys.schemas as sc on O.schema_id = sc.schema_id
               where o.type = ''U'' and avg_fragmentation_in_percent > 20 and (I.name is not null) 
               ORDER BY  avg_fragmentation_in_percent DESC'
select * from @Tbl
go

您無法通過加入 sys.indexes/sys.objects 來獲取索引資訊，因為這些 DMV 是特定於數據庫的，因此僅顯示與目前數據庫相關的資訊。

您可以使用以下查詢獲取您需要的一些資訊 - 它返回表名稱而不是索引名稱，但這可能是合適的：

SELECT DB_NAME(database_id) AS [Database Name],
   OBJECT_NAME(object_id, database_id) AS [Table Name], 
   [index_type_desc] AS [Index Type], 
   page_count AS [Number Of Pages],
   [avg_fragmentation_in_percent] AS [Percentage Of Fragementation]
FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'SAMPLED')

否則，您將不得不向 SQL Server 部署一個過程來獲取索引名稱的資訊。

引用自：https://dba.stackexchange.com/questions/238288