‫نگهداری ایندکس‌ها در اس‌کیوال سرور

USE dbName;
SELECT OBJECT_NAME(DMV.object_id) AS TABLE_NAME,
      SI.NAME AS INDEX_NAME,
      avg_fragmentation_in_percent AS FRAGMENT_PERCENT,
      DMV.record_count
FROM   sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS
      DMV
      LEFT OUTER JOIN SYS.INDEXES AS SI
           ON  DMV.OBJECT_ID = SI.OBJECT_ID
           AND DMV.INDEX_ID = SI.INDEX_ID
WHERE  avg_fragmentation_in_percent > 10
      AND index_type_desc IN ('CLUSTERED INDEX', 'NONCLUSTERED INDEX')
      AND DMV.record_count >= 2000
ORDER BY
      TABLE_NAME DESC

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR 
FOR
   SELECT OBJECT_SCHEMA_NAME([object_id]) + '.' + 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