Friday, July 2, 2010

Find table and index name for fragmented indexes

SELECT  
 OBJECT_NAME(object_id) AS tblName 
,(SELECT name FROM sys.indexes WHERE object_id = a.object_id and index_id = a.index_id) 
,avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) a
WHERE avg_fragmentation_in_percent > 30 
AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX') 
ORDER BY avg_fragmentation_in_percent DESC 

0 comments:

Post a Comment