SQL Server - List all tables and the fragmentation percentage

This is another check you should perform once databases start to get large, as having fragmented indexes have a huge performance hit.For more on what fragmented indexes are, read this fantastic article.After significant trial and error we have come up with an SQL script that lists all indexes for every table and shows the percentage fragmentation for the selected database on your server in one go! 

SELECTa.database_id,db_name(a.database_id) As DatabaseName,a.page_count,a.OBJECT_ID,a.index_id,b.name,a.avg_fragmentation_in_percentFROMsys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS aINNER JOIN sys.indexes AS b ON a.OBJECT_ID = b.OBJECT_ID AND a.index_id = b.index_idWHEREa.database_id = DB_ID()ORDER BYa.OBJECT_ID