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.