12/5/12

Just a sample script of refreshing indexes in the current database

-- To rebuild or reorganize just the "few" fragmented of all indexes in the current database...
-- And remember, this is just a sample of how you could do it.
-- See this as inspiration rather than the "best way"
DECLARE
iterator CURSOR FOR SELECT'ALTER INDEX [' + INDEX_NAME + '] ON [' + SCHEMA_NAME + '].[' + TABLE_NAME + '] ' +CASEWHEN avg_fragmentation_in_percent < 30 AND page_count > 200 THEN 'REORGANIZE'ELSE 'REBUILD'END stmtFROM (SELECTc.name SCHEMA_NAME, t
.name TABLE_NAME, i
.name INDEX_NAME, s
.avg_fragmentation_in_percent, s
.page_count,i.indid FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) sINNER JOIN sys.tables t ON s.object_id = t.object_idINNER JOIN sys.sysindexes i ON s.object_id = i.id AND s.index_id = i.indidINNER JOIN sys.schemas c ON t.schema_id = c.schema_idWHERE s.avg_fragmentation_in_percent >= 10 AND i.indid > 0) mORDER BY m.indid ASCDECLARE
@stmt varchar(max)OPEN iteratorFETCH NEXT FROM iterator INTO @stmtWHILE @@FETCH_STATUS = 0BEGIN
-- PRINT
'EXECUTING: ' + @stmt -- Just if you want some outputEXEC (@stmt)FETCH NEXT FROM iterator INTO @stmtEND
CLOSE
iteratorDEALLOCATE iterator

No comments:

Post a Comment