Display Fragmentation Information of Data and Indexes of Database Table

Display Fragmentation Information of Data and Indexes of Database Table
SQL Server 2000 it was easy to find using DBCC SHOWCONTIG command.
SQL Server 2005 has sys.dm_db_index_physical_stats dynamic view which returns size and fragmentation information for the data and indexes of the specified table or view. You can run following T-SQL for any database to know detailed information of the database.

SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('test_contig'), NULL, NULL , 'DETAILED')

Above query returns lots of information, most of the time we only need to know Tablename, IndexName and Percentage of Fragmentation. Following query returns only three most important details mentioned earlier. I have added an extra condition where results are filtered where average fragmentation is greater than 20%.

SELECT OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.object_id = indexstats.object_id
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20