-- 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 + '] ' +
CASE
WHEN avg_fragmentation_in_percent < 30 AND page_count > 200 THEN 'REORGANIZE'
ELSE 'REBUILD'
END stmt
FROM (
SELECT
c.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) s
INNER JOIN sys.tables t ON s.object_id = t.object_id
INNER JOIN sys.sysindexes i ON s.object_id = i.id AND s.index_id = i.indid
INNER JOIN sys.schemas c ON t.schema_id = c.schema_id
WHERE s.avg_fragmentation_in_percent >= 10 AND i.indid > 0) m
ORDER BY m.indid ASC
DECLARE
@stmt varchar(max)
OPEN iterator
FETCH NEXT FROM iterator INTO @stmt
WHILE @@FETCH_STATUS = 0
BEGIN
-- PRINT 'EXECUTING: ' + @stmt -- Just if you want some output
EXEC (@stmt)
FETCH NEXT FROM iterator INTO @stmt
END
CLOSE iterator
DEALLOCATE iterator
