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;
  • 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? – Aaron Bertrand 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 – Danielle Paquette-Harvey Aug 27 '18 at 15:32
  • Why are you re-inventing what Ola already provides for you for free? – Aaron Bertrand 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
  • 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