SELECT @@SERVERNAME, @@VERSION, GETDATE() as BatchStartTime
exec master.sys.sp_MSforeachdb ' USE [?];
DECLARE @starttime datetime, @endtime datetime
SELECT @starttime = GETDATE()
SELECT db_name() as CurrentDB, @starttime as DBStartTime
SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id, DB_ID()) as ObjectName,
a.index_id, b.name as IndexName,
avg_fragmentation_in_percent, page_count, index_depth, index_type_desc, alloc_unit_type_desc
-- , record_count, avg_page_space_used_in_percent --(null in limited)
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id
--WHERE index_id > 0 -- exclude heaps
ORDER BY DatabaseName asc, avg_fragmentation_in_percent desc, ObjectName asc, IndexName asc
SELECT @endtime = GETDATE()
SELECT @starttime as StartTime, @endtime as EndTime, DATEDIFF(MINUTE,@starttime,@endtime) as TotalMinutes
'

Index fragmentation in all db's per instance

sql

SQL