Script to rebuild all indexes in the database. Indexes are iterated using the cursor and so it can be easily extended with other features.
DECLARE @Database NVARCHAR(128)
DECLARE @Table NVARCHAR(128)
DECLARE @Stmt NVARCHAR(MAX)
DECLARE curTable CURSOR FOR
SELECT '[' + sch.name + '].[' + t.name + ']' as TableName
FROM sys.tables t
inner join sys.schemas sch on sch.schema_id = t.schema_id
ORDER BY t.name
OPEN curTable
FETCH NEXT FROM curTable INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '-->' + @Table
SET @Stmt = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD;'
PRINT @Stmt
EXECUTE(@Stmt)
FETCH NEXT FROM curTable INTO @Table
END
CLOSE curTable
DEALLOCATE curTable
GO
Same version as the above on but it also includes the option to set different fillfactor.
DECLARE @Database NVARCHAR(128)
DECLARE @Table NVARCHAR(128)
DECLARE @FillFactor INT
DECLARE @Stmt NVARCHAR(MAX)
SET @FillFactor = 100
DECLARE curTable CURSOR FOR
SELECT '[' + sch.name + '].[' + t.name + ']' as TableName
FROM sys.tables t
inner join sys.schemas sch on sch.schema_id = t.schema_id
ORDER BY t.name
OPEN curTable
FETCH NEXT FROM curTable INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '-->' + @Table
SET @Stmt = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD ' +
'WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3), @FillFactor) + ')'
PRINT @Stmt
EXECUTE(@Stmt)
FETCH NEXT FROM curTable INTO @Table
END
CLOSE curTable
DEALLOCATE curTable
GO