Rob Symonds

Thanks for script ..

This work fine but actually I need all ALTER TSQL will be in output so I just copy and then run .

I just modified your script and now in action column I am getting all ALTER Script but it is difficult to take one by one and then run insted of this what I want is that I need all these TSQL Command in one output so i just need to run this once.

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 'ALTER INDEX ' + i.name + ' ON ' +o.name +' REORGANIZE'

when stats.avg_fragmentation_in_percent > 30 then 'ALTER INDEX ' + i.name + ' ON ' +o.name +' 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 > 20.0

AND

stats.index_id > 0

ORDER BY

objectname

Thanks in advance...

example :- I need output like that

ALTER INDEX PK__ACH_TRANSFER_DTL__00551192 ON ACH_TRANSFER_DTLS REBUILD

ALTER INDEX PK__CONFIG_AUDIT_LOG__269AB60B ON CONFIG_AUDIT_LOG REBUILD

ALTER INDEX PK__CUSTOMER_ACCOUNT__0425A276 ON CUSTOMER_ACCOUNT_VER_LOG REBUILD

.... like all ALTER coomand which generated from this will be in output.

Thanks!