Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Info
titleWARNING

The following script may cause SQL performance degradation.  Consider running when the server is not in use.

Defragment Indexes

Code Block
languagesql
titleFix Fragmented Indexes
DECLARE @TableName VARCHAR(255)
DECLARE @SQL NVARCHAR(500)
DECLARE @FILLFACTOR INT
SET @FILLFACTOR = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([OBJECT_ID])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@FILLFACTOR) + ')'
EXEC (@SQL)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

...