Identify Table Fragmentation and Create a Statement Plan for SQL Server
use MyDatabaseName
go

declare @Offenders table
(
    [Schema] varchar(100),
    [TableName] varchar(100),
    [IndexName] varchar(200),
    [PercentFragmented] float,
    [PageCount] int,
    [RecommendedAction] nvarchar(max)
);

insert @Offenders

select
    *,
    case when temp.PercentFragmented > 30
        then    ('alter index ' + temp.IndexName + ' on ' + temp.TableName + ' rebuild with (fillfactor = 90);')
        else    ('alter index ' + temp.IndexName + ' on ' + temp.TableName + ' reorganize;')
    end as RecommendedAction

from
(
    select
        s.name                              as 'Schema',
        t.name                              as 'TableName',
        i.name                              as 'IndexName',
        ddips.avg_fragmentation_in_percent  as 'PercentFragmented',
        ddips.page_count                    as 'PageCount'

    from
        sys.dm_db_index_physical_stats (db_id(), null, null, null, null) as ddips
        inner join sys.tables as t on t.object_id = ddips.object_id
        inner join sys.schemas as s on s.schema_id = t.schema_id
        inner join sys.indexes as i on
            i.object_id = ddips.object_id
        and i.index_id = ddips.index_id
    where
        ddips.database_id = db_id()
    and i.name is not null
    and ddips.avg_fragmentation_in_percent > 0
) temp

select 
    * 
from
    @Offenders
where
    [PercentFragmented] > 10
order by
    [PageCount] desc,
    [PercentFragmented] desc