SQL INDEX REBUILD

To rebuild an index

1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
2. Expand Databases, expand the database that contains the table with the specified index, and then expand Tables.
3. Expand the table in which the index belongs and then expand Indexes.
4. Right-click the index to rebuild and then click Rebuild.
5. To start the rebuild operation, click OK.

To rebuild all indexes on a table

1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
2. Expand Databases, expand the database that contains the table with the specified indexes, and then expand Tables.
3. Expand the table in which the indexes belong.
4. Right-click Indexes and then click Rebuild All.
5. To start the rebuild operation, click OK.

 Other way
maintain multiple database and monitoring the disk fragmentation and rebuilding the indexes for individual database is quite time consuming task. Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages.

 

So for a DBA to rebuild all the indexes for all the database on the SQL Server with one script will help them in their database maintenance task.

Here is the script that will rebuild all the indexes for all the tables in your SQL Server database. This script can be used in a maintenance plan and can be executed in a scheduled job.

SQL

Edit|Remove

USE DatabaseName --Enter the name of the database you want to reindex 
 
  
 
DECLARE @TableName varchar(255) 
 
  
 
DECLARE TableCursor CURSOR FOR 
 
SELECT table_name FROM information_schema.tables 
 
WHERE table_type = 'base table' 
 
  
 
OPEN TableCursor 
 
  
 
FETCH NEXT FROM TableCursor INTO @TableName 
 
WHILE @@FETCH_STATUS = 0 
 
BEGIN 
 
DBCC DBREINDEX(@TableName,' ',90) 
 
FETCH NEXT FROM TableCursor INTO @TableName 
 
END 
 
  
 
CLOSE TableCursor 
 
  
 
DEALLOCATE TableCursor 

USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO