MSSQL: Script to reorg vCenter indexes in SQL Server

Script to reorg vcenter indexes above 70% fragmentations.

SQLCMD -i d:\reorf.sql -o d:\reorg.txt

use vcdb

select 'alter index ' +  ind.name + ' on ' + OBJECT_NAME(ind.OBJECT_ID) + ' reorganize;' from sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind  ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id

 WHERE indexstats.avg_fragmentation_in_percent > 70  ORDER BY indexstats.avg_fragmentation_in_percent DESC;

Changed database context to 'VCDB'.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

alter index VPXI_EVENT_EVENT_TYPE on VPX_EVENT reorganize;

alter index VPX_EVENT_ARG_F1 on VPX_EVENT_ARG reorganize;

alter index PK_VPX_DBM_COUNTER_VALUE on VPX_DBM_COUNTER_VALUE reorganize;

alter index VPXI_EVENT_CATEGORY on VPX_EVENT reorganize;

alter index VPXI_EVENT_CREATE_TIME on VPX_EVENT reorganize;

alter index VPXI_EVENT_EXTENDED_CLASS on VPX_EVENT reorganize;

alter index VPX_EVENT_ARG_F2 on VPX_EVENT_ARG reorganize;

alter index PK_VPX_SAMPLE_TIME1 on VPX_SAMPLE_TIME1 reorganize;

alter index PK_VPX_SAMPLE_TIME2 on VPX_SAMPLE_TIME2 reorganize;

alter index PK_VPX_SAMPLE_TIME3 on VPX_SAMPLE_TIME3 reorganize;

alter index VPX_SAMPLE_TIME3_U1 on VPX_SAMPLE_TIME3 reorganize;

alter index PK_VPX_TABLE on VPX_TABLE reorganize;

(12 rows affected)