/ Published in: SQL
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
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 '