Rebuild all indexes in a Database

Manually Rebuild Indexing 


Indexes can be rebuilt/reorganize in SQL Server Management Studio using the following steps;

1- In Object Explorer locate the table that holds the intended index and expand it.
2- Expand Indexes.
3- Right-click on the index and click on Rebuild or Reorganize.









Rebuild All Table Indexing By Script


Try the following script:

Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'
GO

Also


I prefer(After a long search) to use the following script, it contains @fillfactor determines how much percentage of the space on each leaf-level page is filled with data.


DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80 
DECLARE TableCursor CURSOR FOR
SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))+'.' + QUOTENAME(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



Comments

Popular posts from this blog

How to Check Index Fragmentation on Indexes in a Database

Private Chat Using Node js and socket.io with encrypt and decrypt message and insert into mongo db