Check Fragmentation on All Indexes on Database - SQL Server Central

Check Fragmentation on All Indexes on Database - SQL Server Central

/*
In this script we create a temporal table and then review all indexes with next conditions.
If the index have avg_fragmentation_in_percent > 10 % or avg_page_space_used_in_percent > 90 % 
then we have run alter index ... on ... with rebuild 
*/

SELECT sysobj.name object_name, 
 sysobj.xtype object_type, 
 indexes.name index_name, 
 index_data.database_id, 
    index_data.object_id, 
    index_data.index_id, 
    index_data.avg_fragmentation_in_percent, 
    index_data.avg_fragment_size_in_pages,
    index_data.avg_page_space_used_in_percent,
    index_data.record_count
Into #fragmentados 
FROM sys.dm_db_index_physical_stats (6, NULL,NULL, NULL,'SAMPLED') index_data -- Review all tables on database
inner join 
sys.sysobjects sysobj 
on index_data.object_id = sysobj.id 
left outer join sys.sysindexes indexes 
on index_data.index_id = indexes.indid 
and index_data.object_id = indexes.id
WHERE (avg_fragmentation_in_percent > 10
OR avg_page_space_used_in_percent < 90)
-- avg_fragmentation_in_percent (sys.dm_db_index_physical_stats) / logical scan fragmentation (dbcc showcontig) < 10%
-- avg_fragment_size_in_page (sys.dm_db_index_physical_stats) / Extent Scan Fragmentation (dbcc showcontig)
-- avg_page_space_used_in_percent (sys.dm_db_index_physical_stats) / Avg. Page Density (dbcc showcontig) > 90 %