Get All Index Fragmentation Levels

The modern fragmentation report: this query calls dm_db_index_physical_stats for the current database and lists every table and index (heaps included) with its average fragmentation percentage and page count, sorted worst first. The quick companion to the older DBCC SHOWCONTIG-based scripts.

SELECT
	schema_name(SysT.[schema_id]) as [Schema], 
	SysT.[name] as [Table], 
	IsNull(SysI.[name], '<headp>') as [Index],
	Convert(Decimal(16, 2), IPS.avg_fragmentation_in_percent) [Fragmentation %],
	IPS.page_count as [Page Count]
FROM
	sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS IPS
INNER JOIN sys.tables SysT
	on SysT.[object_id] = IPS.[object_id]
INNER JOIN sys.indexes AS SysI
	ON SysI.[object_id] = IPS.[object_id]
	AND IPS.index_id = SysI.index_id
WHERE
	IPS.database_id = DB_ID()
ORDER BY
	IPS.avg_fragmentation_in_percent desc