====== Rebuild indexes ======

Rebuild fragmented indexes on a specified table:

<code tsql>CREATE OR ALTER PROCEDURE dbo.usp_Rebuild_Indexes (
  @objName NVARCHAR(255)
, @rebuildThreshold INT = 10
, @online BIT = 0
, @execute BIT = 1
, @debug BIT = 0
)
AS
 
DECLARE @sql NVARCHAR(MAX) = ''
 
SELECT
  @sql +='
 
ALTER INDEX [' + i.[name] + ']
ON [' + OBJECT_SCHEMA_NAME(i.[object_id]) + '].[' + OBJECT_NAME(i.[object_id]) + ']
' + IIF(@online = 1, 'REORGANIZE', 'REBUILD')
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ips
  INNER JOIN sys.indexes i 
    ON i.[object_id] = ips.[object_id]
    AND i.index_id = ips.index_id
WHERE i.[object_id] = OBJECT_ID(@objName)
AND ips.avg_fragmentation_in_percent > = @rebuildThreshold

IF @debug = 1 PRINT @sql
IF @execute = 1 EXEC (@sql)
</code>

{{tag> }}