Microsoft SQL Server Database Rebuild Index Script
SQL Server script to rebuild all indexes for all tables in database
Purpose of index rebuilding
Rebuilding an index means deleting the old index replacing it with a new index. Performing an index rebuild eliminates fragmentation, compacts the pages based on the existing fill factor setting to reclaim storage space, and also reorders the index rows into contiguous pages.
When we do index rebuilding
Microsoft recommends fixing index fragmentation issues by rebuilding the index if the fragmentation percentage of the index exceeds 30%
The below script will work with SQL Server 2005 and later versions.
The script below allows you to rebuild indexes for all tables within a database.
Use [DBNAME] -- Change Database name
Go
SELECT
'Alter INDEX [' +dbindexes.[name] +'] ON ' + dbtables.[name] + ' REBUILD ' FragmentedIndexes,
dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count,indexstats.*
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() AND dbtables.[name] like '%%' and
indexstats.avg_fragmentation_in_percent>=50 ---Change if required to check less fragmentation perc
and dbindexes.[name] is not null
ORDER BY indexstats.avg_fragmentation_in_percent desc
Go