Display database fragmentation for each table.

database_fragmentation.sql

-- USE AdventureWorks;
SELECT 
  s.name AS 'sys_schema', 
  t.name AS 'sys_table', 
  i.name AS 'sys_index',
  x.alloc_unit_type_desc,
  x.avg_fragmentation_in_percent,
  x.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) x
INNER JOIN sys.tables t ON t.object_id = x.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.indexes i ON i.object_id = x.object_id AND x.index_id = i.index_id
WHERE x.database_id = DB_ID()
ORDER BY x.avg_fragmentation_in_percent DESC;