Scribd
Upload
61 views1 page

Rebuilding Indexes On SQL Server Script

This SQL query selects indexes from tables in the current database that have over 30% fragmentation and generates ALTER INDEX statements to rebuild them online.

Uploaded by

Davison Gudo
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
61 views1 page

Rebuilding Indexes On SQL Server Script

This SQL query selects indexes from tables in the current database that have over 30% fragmentation and generates ALTER INDEX statements to rebuild them online.

Uploaded by

Davison Gudo
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

SELECT 'alter index ' + dbindexes.[name] + ' ON ' + dbtables.

[name] + '
rebuild with (online=ON);' ,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN [Link] dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN [Link] dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN [Link] AS dbindexes ON dbindexes.[object_id] = indexstats.
[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() and indexstats.avg_fragmentation_in_percent
> 30 and dbindexes.index_id <> 0
ORDER BY indexstats.avg_fragmentation_in_percent desc

You might also like