SQL Server - Index Fragmentation

indexFragmentation.sql

SELECT t.name as table_name, i.name as index_name, s.index_type_desc, s.avg_fragmentation_in_percent, s.page_count
  FROM sys.dm_db_index_physical_stats (
         DB_ID('<db name>')
       , NULL --OBJECT_ID('dbo.<table name>')
       , NULL
         -- NULL to view all indexes;
         -- otherwise, input index number
       , NULL -- NULL to view all partitions of an index
       , NULL -- 'DETAILED') as s
  join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id
  join sys.tables t on t.object_id = s.object_id
 where avg_fragmentation_in_percent > 30
   and page_count > 1000
 order by 1, 2