Advanced Search

Return to Snippet

Revision: 25477
at March 31, 2010 08:43 by tvanzele
Initial Code 
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
'
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

                                    

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
Index fragmentation in all db's per instance

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    
sql

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
SQL