Reconstruir tots els indexs d’una base de dades i especificar un factor d’ompliment concret:
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @Database = 'SBODEMOES'
SET @fillfactor = 95
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
WHERE table_type = ''BASE TABLE'''
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
BEGIN
-- SQL 2005 or higher command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)
END
ELSE
BEGIN
-- SQL 2000 command
DBCC DBREINDEX(@Table,' ',@fillfactor)
END
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursorEXEC sp_MSforeachtable @command1="print 'rebuilding indexes in table: ?'", @command2="ALTER INDEX ALL ON ?
REBUILD WITH (FILLFACTOR=95)";