This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing SQL Server Database Administrator script for rebuilding all Indexes of a Database.
To Rebuild or Reorganize the Indexes is a very common task for DBA.
If Index fragmentation is increasing, periodically it requires executing Rebuild Index for better performance.
If we want to Rebuild all Indexes of a Database, we should build and generate the script because using default command we can only perform Rebuild operation on table level.
SQL Server: Script to find Index Average Fragmentation in Percentage
In below script, I commented Fill Factor related changes, and If anyone wants, they uncomment it.
Please SELECT your database and execute the below script.
Note: Database REBUILD INDEX is a very costly operation and It requires an exclusive lock on table so please aware yourself before executing it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
DECLARE @DatabaseName SYSNAME = DB_NAME() DECLARE @TableName VARCHAR(256) --DECLARE @FILLFACTOR INT = 85 DECLARE @SQL NVARCHAR(MAX) = 'DECLARE curAllIndex CURSOR FOR SELECT TABLE_SCHEMA + ''.'' + TABLE_NAME AS TABLENAME FROM ' + @DatabaseName + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE''' BEGIN EXEC sp_executeSQL @SQL OPEN curAllIndex FETCH NEXT FROM curAllIndex INTO @TableName WHILE (@@FETCH_STATUS = 0) BEGIN /* -- For using FillFactor setting. SET @DynamicSQL = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR,@FILLFACTOR) + ')' */ SET @SQL = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD ' PRINT @SQL EXEC sp_executeSQL @SQL FETCH NEXT FROM curAllIndex INTO @TableName END CLOSE curAllIndex DEALLOCATE curAllIndex END |
Leave a Reply