Complete a Server Assessment – Index Fragmentation for an Object

Below you will find the code for the Index Fragmentation for an object.  Simply select the text and copy to the Clipboard.

This code can be used if you would like to dig deeper in to the Indexes of a particular table.  After you copy it, you will need to enter the table name in the WHERE clause.

SELECT OBJECT_NAME(object_id) AS ‘Table_Name’,
(SELECT name
FROM sys.indexes
WHERE (object_id = i.object_id) AND (index_id = i.index_id)) AS ‘Index_Name’
, CONVERT(DECIMAL(9,2),avg_fragmentation_in_percent) AS ‘avg_fragmentation_in_percent’
, page_count
, index_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS i
WHERE OBJECT_NAME(object_id) = ‘<<<< Enter Object Name >>>>’
ORDER BY avg_fragmentation_in_percent DESC

This code was written using the Microsoft SQL Server documentation.  If you find that this code was copied from else where, please let me know so I can give proper credit.