如何使用TSQL从实例中的所有数据库中选择有关具有指定列的索引的信息？

Question

此查询从当前数据库中选择所有索引--名称、碎片和其他一些参数。

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;

Answer 1

您可以在游标中执行动态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