This query selects all the indexes – its names, fragmentation, and some other parameters from the current database.
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;
Now I need to select all the indexes with specified columns from all databases. I can use USE [dbname]
when I execute the statement manually, but I need to put it in a cursor, so I cannot write USE [dbname]
in a cursor.
How can I substitute the database selection in the ...CROSS APPLY sys.dm_db_index_physical_stats( DB_ID(), ...
part of my statement?
Help will be much appreciated to show how can I select all database indexes with a single query.
Something like this:
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;
Best Answer
You can do dynamic SQL in a cursor to do it on every database on the server that way :