• I'm not sure I understand your request entirely. Are you looking for a script to create the TSQL commands for you to use elsewhere or are you looking for a script to actually execute the commands?

    If you are just looking to generate the TSQL, you should be able to adapt this script to your needs by playing with the output in the CASE statement. In other words, you could change:

    when stats.avg_fragmentation_in_percent < 30 then 'Reorganize'

    to something like

    when stats.avg_fragmentation_in_percent < 30 then 'ALTER INDEX ' + i.name + ' REORGANIZE...'

    I've read that reorganizing/reindexing doesn't make much difference if the index is in the 5k to 10k page range. So this script doesn't look for anything under 5k pages.

    use AdventureWorks

    SELECT

    stats.object_id AS objectid,

    QUOTENAME(o.name) AS objectname,

    QUOTENAME(s.name) AS schemaname,

    stats.index_id AS indexid,

    i.name AS index_name,

    stats.partition_number AS partitionnum,

    stats.avg_fragmentation_in_percent AS frag,

    stats.page_count,

    CASE

    when stats.avg_fragmentation_in_percent < 30 then 'Reorganize'

    when stats.avg_fragmentation_in_percent > 30 then 'Rebuild'

    END AS 'action_to_take'

    FROM

    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, 'LIMITED') as stats,

    sys.objects AS o,

    sys.schemas AS s,

    sys.indexes AS i

    WHERE

    o.object_id = stats.object_id

    AND

    s.schema_id = o.schema_id

    AND

    i.object_id = stats.object_id

    AND

    i.index_id = stats.index_id

    AND

    stats.avg_fragmentation_in_percent >= 10.0

    AND

    stats.page_count >= 5000

    AND

    stats.index_id > 0

    ORDER BY

    action_to_take