如何使用 TSQL 從實例中的所有數據庫中選擇有關具有指定列的索引的資訊？

此查詢從目前數據庫中選擇所有索引 - 它的名稱、碎片和一些其他參數。

select
   idx.[name] as [index_name],
   sc.[name] as [schema_name],
   obj.[name] as [table_name],  
   ips.[avg_fragmentation_in_percent] as [fragmentation_percent]
from sys.indexes as idx
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sc  on obj.schema_id = sc.schema_id
cross apply sys.dm_db_index_physical_stats( DB_ID(), idx.object_id, idx.index_id, NULL ,'LIMITED') AS ips
where idx.[name] is not NULL 
order by [fragmentation_percent] desc;

現在我需要從所有數據庫中選擇具有指定列的所有索引。當我手動執行語句時可以使用USE [dbname]，但我需要將它放在游標中，所以我不能寫USE [dbname]在游標中。

如何在...CROSS APPLY sys.dm_db_index_physical_stats( DB_ID(), ...我的語句部分替換數據庫選擇？

非常感謝幫助顯示如何使用單個查詢選擇所有數據庫索引。

像這樣的東西：

declare cr_index cursor
for
exec(@dynamicUSE)
select
   idx.[name] as [index_name],
   sc.[name] as [schema_name],
   obj.[name] as [table_name],  
   ips.[avg_fragmentation_in_percent] as [fragmentation_percent]
from sys.indexes as idx
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sc  on obj.schema_id = sc.schema_id
cross apply sys.dm_db_index_physical_stats( DB_ID(), idx.object_id, idx.index_id, NULL ,'LIMITED') AS ips
where idx.[name] is not NULL 
order by [fragmentation_percent] desc;

您可以在游標中執行動態 SQL 以在伺服器上的每個數據庫上執行此操作：

DECLARE @DB_Name varchar(100) 
DECLARE @Command nvarchar(1000)
DECLARE database_cursor CURSOR FOR 
SELECT name FROM MASTER.sys.sysdatabases 

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0 
BEGIN 
SELECT @Command = 'USE [' +  @DB_Name + '];  select
  idx.[name] as [index_name],
  sc.[name] as [schema_name],
  obj.[name] as [table_name],  
  ips.[avg_fragmentation_in_percent] as [fragmentation_percent]
from sys.indexes as idx
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sc  on obj.schema_id = sc.schema_id
cross apply sys.dm_db_index_physical_stats( DB_ID(), idx.object_id, idx.index_id, NULL ,''LIMITED'') AS ips
where idx.[name] is not NULL 
order by [fragmentation_percent] desc;'
EXEC sp_executesql @Command

FETCH NEXT FROM database_cursor INTO @DB_Name 
END

CLOSE database_cursor 
DEALLOCATE database_cursor

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