I am trying to compile a index fragmentation report on all databases, in a multi-server environment. The query will be executed via an openquery command, so I would like to make it as simple as possible, without using stored procedures, temporary tables, etc.

I am aware the follow can bring back all indexes on all databases

select * from sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'SAMPLED')

but as soon as we join it to sys.indexes, sys.objects, we restrict out system wide indexes and rather focus on indexes in the currently executing database.

Is there a way around this, so I can get the following output for all databases:

Database Name, Index Name, Index Type, Number of Pages, Percentage of Fragmentation.

  • Do you want this to work for SQL 2008 or SQL 2012? The tags are a bit unclear May 16, 2019 at 7:58
  • I'm in an environment that includes 2008 - 2012, so preferably it would work across both versions, but I am opening to deploying different versions.
    – Asher
    May 16, 2019 at 9:37

3 Answers 3


It's not possible to achieve without using temp table as the DMVs/DMFs that we are using are database scoped and requirement is server scoped, and i don't see any difficulties or complication using temp table as follows:

Declare @Tbl table (ServerName varchar(128), DBName varchar(128), SchemaName varchar(128), TableName varchar (100), IndexName varchar (100), FragPercent float, IndexType tinyint, IsPrimaryKey bit);

Insert into @Tbl
exec SP_MSforeachdb @command1 = 
            'use [?];
                select  @@Servername, 
                        sc.name as SchemaName,
                        object_name (s.object_id) as TableName, 
                from sys.dm_db_index_physical_stats (DB_ID (), NULL, NULL, NULL, ''LIMITED'') as S
                    join sys.indexes as I on s.object_id = I.object_id and s.index_id = I.index_id
                    join sys.objects as O on s.object_id = O.object_id 
                    join sys.schemas as sc on O.schema_id = sc.schema_id
                where o.type = ''U'' and avg_fragmentation_in_percent > 20 and (I.name is not null) 
                ORDER BY  avg_fragmentation_in_percent DESC'
select * from @Tbl
  • Perfect query, I have just comment "o.type = ''U'' and avg_fragmentation_in_percent > 20 and " to get all tables Mar 27, 2020 at 9:36
  • Very minor point, but this uses a table variable, not a temp table. The two are not the same. Feb 5 at 17:59

You can't get the index information by joining to sys.indexes/sys.objects as you've found because these DMVs are database specific, so only show the information related to the current database.

You can get some of the information you need using the below query - it returns Table Name instead of Index Name but that might be suitable:

SELECT DB_NAME(database_id) AS [Database Name],
    OBJECT_NAME(object_id, database_id) AS [Table Name], 
    [index_type_desc] AS [Index Type], 
    page_count AS [Number Of Pages],
    [avg_fragmentation_in_percent] AS [Percentage Of Fragementation]
FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'SAMPLED')

Otherwise, you're going to have to deploy a procedure to your SQL Servers to fetch the information for Index Name.


The first parameter is Database ID that you could found in sys.databases table.

Below query may help you for suggestions:

--SELECT * FROM sys.databases
DECLARE @dbid int = 9

SELECT  dbschemas.[name] as 'Schema',
        dbtables.[name] as 'Table',
        dbindexes.[name] as 'Index',
        CASE WHEN indexstats.page_count <= 100 OR indexstats.avg_fragmentation_in_percent < 10 THEN '--Do nothing. It''s good'
             WHEN indexstats.avg_fragmentation_in_percent BETWEEN 10 AND 30 THEN 'ALTER INDEX '+dbindexes.[name]+' ON '+dbschemas.[name]+'.'+dbtables.[name]+' REORGANIZE;'
             ELSE 'ALTER INDEX '+dbindexes.[name]+' ON '+dbschemas.[name]+'.'+dbtables.[name]+' REBUILD;'
FROM    sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, NULL) AS indexstats
JOIN    sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
JOIN    sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
JOIN    sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id
WHERE   indexstats.database_id = @dbid
ORDER BY indexstats.avg_fragmentation_in_percent DESC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.