SQLServer rebuild index

rebuild_index.sql

DECLARE @SchemaName sysname, @TableName sysname, @IndexName sysname 
DECLARE @basesql nvarchar(max), @sql nvarchar(max) 

SET @basesql = 'ALTER INDEX @1 On @2 REBUILD WITH ( FILLFACTOR = 50 ) '

DECLARE IXC CURSOR FOR 
SELECT 
    OBJECT_SCHEMA_NAME(i.object_id) As SchemaName
    , OBJECT_NAME(i.object_id) AS TableName 
    , i.name AS IndexName 
FROM 
    sys.indexes i 
	inner join sys.dm_db_index_physical_stats (DB_ID(N'FA'), NULL, NULL, NULL, NULL) s
	on i.object_id = s.object_id and i.index_id = s.index_id
WHERE 
    OBJECT_SCHEMA_NAME (i.object_id) <> 'sys' 
    AND 
    i.index_id > 0 
	AND s.avg_fragmentation_in_percent > 30
	AND s.fragment_count > 1000

OPEN IXC

FETCH NEXT FROM IXC 
INTO @SchemaName, @TableName, @IndexName

WHILE @@FETCH_STATUS = 0 
BEGIN 
    PRINT @SchemaName + '.' + @TableName + ':' + @IndexName

    SET @sql = REPLACE(@basesql, '@1', @IndexName) 
    SET @sql = REPLACE(@sql, '@2', @SchemaName + '.' + @TableName) 
    
    EXECUTE (@sql)

    FETCH NEXT FROM IXC 
    INTO @SchemaName, @TableName, @IndexName    
END

CLOSE IXC 
DEALLOCATE IXC