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

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

How To Migrate MVC 3 Application To MVC 5

Populate a drop-down in Vue.js and Asp.net Core from an ajax call