SQL Server Fragmented Indexes

sql_server_frag_index.sql

select
    i.[object_id],
    i.[index_id],
    OBJECT_SCHEMA_NAME( i.object_id, DB_ID() ) as SchemaName,
    t.[name] as TableName,
    i.[name] as IndexName,
    (
	    case i.[fill_factor]
	        when 0 then 100
	        else i.fill_factor
	    end
    ) as [FillFactor],
    PhysicalStats.[avg_fragmentation_in_percent],
    PhysicalStats.[pages]
from sys.dm_db_index_usage_stats as ddius
	inner join sys.indexes as i on ddius.object_id = i.object_id and ddius.index_id = i.index_id
    inner join sys.tables as t on ddius.[object_id] = t.[object_id]
    inner join (
        select
            database_id,
            object_id,
            index_id,
            max( avg_fragmentation_in_percent ) as avg_fragmentation_in_percent,
            sum( page_count ) as pages
        from
            sys.dm_db_index_physical_stats
            ( DB_ID(), NULL, NULL, NULL, 'SAMPLED' )
        group by
            database_id,
            object_id,
            index_id
    ) as PhysicalStats on
        ddius.[database_id] = PhysicalStats.[database_id]
        and ddius.[object_id] = PhysicalStats.[object_id]
        and ddius.[index_id] = PhysicalStats.[index_id]
where
    PhysicalStats.[avg_fragmentation_in_percent] >= 10
    and PhysicalStats.[pages] >= 100
order by
    PhysicalStats.avg_fragmentation_in_percent desc,
    TableName,
    IndexName

sql_server_frag_index_2.sql

-- Another option...

SELECT 
	OBJECT_NAME(i.OBJECT_ID) AS TableName, 
	i.name AS IndexName,
	indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats 
	INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID 
WHERE 
	indexstats.avg_fragmentation_in_percent > 30 
	AND i.index_id = indexstats.index_id
order by
	indexstats.avg_fragmentation_in_percent desc
,	OBJECT_NAME(i.OBJECT_ID)