Advanced Search

Index fragmentation in all db's per instance

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. SELECT @@SERVERNAME, @@VERSION, GETDATE() AS BatchStartTime
  2. EXEC master.sys.sp_MSforeachdb ' USE [?];
  3. DECLARE @starttime datetime, @endtime datetime
  4. SELECT @starttime = GETDATE()
  5. SELECT db_name() as CurrentDB, @starttime as DBStartTime
  6. SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id, DB_ID()) as ObjectName,
  7. a.index_id, b.name as IndexName,
  8. avg_fragmentation_in_percent, page_count, index_depth, index_type_desc, alloc_unit_type_desc
  9. -- , record_count, avg_page_space_used_in_percent --(null in limited)
  10. FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a
  11. JOIN sys.indexes AS b
  12. ON a.object_id = b.object_id AND a.index_id = b.index_id
  13. --WHERE index_id > 0 -- exclude heaps
  14. ORDER BY DatabaseName asc, avg_fragmentation_in_percent desc, ObjectName asc, IndexName asc
  15. SELECT @endtime = GETDATE()
  16. SELECT @starttime as StartTime, @endtime as EndTime, DATEDIFF(MINUTE,@starttime,@endtime) as TotalMinutes
  17. '

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.