1

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;
4
  • 1
    Can you explain exactly why you "need to put it in a cursor"? If you get the results, does it matter it declare cursor was used? Do you need to do something else with each result that you haven't told us about? Aug 27 '18 at 14:54
  • Yes, I want to write a program to automatically rebuild indexes where its fragmentation > 30. It works fine for one database, but I want to run it for all databases in my instance. I want a query to select all the indexes in the instance(I have 4 databases). The query printed above selects indexes only for current database. When I change the database, it works fine for the other one. But I want to know, is it possible to write the select statement the way that it will select all indexes?
    – user159374
    Aug 27 '18 at 14:59
  • 1
    You could do a job on the server that will do it for you. You can use Olga's maintenance script. ola.hallengren.com Aug 27 '18 at 15:32
  • Why are you re-inventing what Ola already provides for you for free? Aug 27 '18 at 15:39
1

You can do dynamic SQL in a cursor to do it on every database on the server that way :

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
2
  • Actually I want to select all the index names into cursor
    – user159374
    Aug 27 '18 at 14:35
  • 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;
    – user159374
    Aug 27 '18 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy