---------------------------------
-- Nicolas SOUQUET - 05/06/2009 -
---------------------------------
CREATE PROCEDURE [dbo].[Ps_Indexes_Rebuild]
AS
BEGIN
  SET NOCOUNT ON
 
  -- Table dans laquelle on va récupérer les noms et caractéristiques des indexes
  DECLARE @Tbindexes TABLE
  (
    DB SYSNAME,
    Tables SYSNAME,
    IndexName SYSNAME,
    Frag VARCHAR(5),
    PageUsage VARCHAR(5),
    Pages VARCHAR(10),
    FillFactors TINYINT,
    Seeks VARCHAR(20),
    Scans VARCHAR(20),
    LastUserSeek VARCHAR(19),
    LastUserScan VARCHAR(19),
    IsUniqueConstraint BIT,
    IsPadded BIT,
    IsHypothetical BIT
  )
 
  -- Table dans laquelle on récupère les statistiques des indexes
  DECLARE @TbIndexesStats TABLE
  (
    database_id SMALLINT,
    object_id INT,
    index_id INT,
    avg_fragmentation_in_percent NUMERIC(4,2),
    avg_page_space_used_in_percent NUMERIC(4,2),
    page_count BIGINT,
    user_seeks BIGINT,
    user_scans BIGINT,
    last_user_seek DATETIME,
    last_user_scan DATETIME
  );
 
  -- Récupération des stats sur les indexes
  WITH CTE_DB AS
  (
    SELECT database_id
    FROM sys.databases
    WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServer', 'ReportServerTempDB', 'distribution')
  )
  INSERT INTO @TbIndexesStats
  SELECT IPS.database_id,
      IPS.object_id,
      IPS.index_id,
      IPS.avg_fragmentation_in_percent,
      IPS.avg_page_space_used_in_percent,
      IPS.page_count,
      IUS.user_seeks,
      IUS.user_scans,
      IUS.last_user_seek,
      IUS.last_user_scan
  FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'SAMPLED') IPS
  JOIN CTE_DB ON CTE_DB.database_id = IPS.database_id
  JOIN sys.dm_db_index_usage_stats IUS
      ON IUS.database_id = IPS.database_id
      AND IUS.object_id = IPS.object_id
      AND IUS.index_id = IPS.index_id
  WHERE IPS.index_id > 0
  AND IPS.avg_fragmentation_in_percent > 25.0
  AND IPS.page_count > 50
 
  -- Récupération des noms et caractéristiques des indexes
  DECLARE @SQL VARCHAR(1024),
      @Database_id VARCHAR(10),
      @Object_id VARCHAR(10),
      @Index_id VARCHAR(10),
      @AvgFragInPercent VARCHAR(5),
      @AvgPageSpaceUsedInPercent VARCHAR(5),
      @PageCount VARCHAR(10),
      @User_seeks VARCHAR(20),
      @User_scans VARCHAR(20),
      @Last_user_seek VARCHAR(19),
      @Last_user_scan VARCHAR(19)
 
  DECLARE curDBIndexes CURSOR FOR
    SELECT CAST(database_id AS VARCHAR(10)),
      CAST(object_id AS VARCHAR(10)),
      CAST(index_id AS VARCHAR(10)),
      CAST(avg_fragmentation_in_percent AS VARCHAR(5)),
      CAST(avg_page_space_used_in_percent AS VARCHAR(5)),
      CAST(page_count AS VARCHAR(10)),
      CAST(user_seeks AS VARCHAR(20)),
      CAST(user_scans AS VARCHAR(20)),
      COALESCE(CONVERT(VARCHAR(19), last_user_seek, 120), 'NULL'),
      COALESCE(CONVERT(VARCHAR(19), last_user_scan, 120), 'NULL')
    FROM @TbIndexesStats
  FOR READ ONLY
 
  OPEN curDBIndexes
  FETCH NEXT FROM curDBIndexes INTO @Database_id, @Object_id, @Index_id, @AvgFragInPercent,
                    @AvgPageSpaceUsedInPercent, @PageCount, @User_seeks, @User_scans,
                    @Last_user_seek, @Last_user_scan
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @SQL = 'SELECT DB_NAME(' + @Database_id + '),' +
            'O.name,' +
            'IDX.name,' +
            @AvgFragInPercent + ',' +
            @AvgPageSpaceUsedInPercent + ',' +
            @PageCount + ',' +
            'IDX.fill_factor, ' +
            @User_seeks + ',' +
            @User_scans + ',''' +
            @Last_user_seek + ''',''' +
            @Last_user_scan + ''',' +
            'IDX.is_unique_constraint, ' +
            'IDX.is_padded, ' +
            'IDX.is_hypothetical' +
          ' FROM ' + DB_NAME(CAST(@Database_id AS INT)) + '.sys.indexes IDX' +
          ' JOIN '  + DB_NAME(CAST(@Database_id AS INT)) + '.sys.objects O ON O.object_id = IDX.object_id' +
          ' WHERE IDX.object_id = ' + @Object_id +
          ' AND IDX.index_id = ' + @Index_id
 
    INSERT INTO @Tbindexes
    EXEC (@SQL)
 
    FETCH NEXT FROM curDBIndexes INTO @Database_id, @Object_id, @Index_id, @AvgFragInPercent,
                      @AvgPageSpaceUsedInPercent, @PageCount, @User_seeks, @User_scans,
                      @Last_user_seek, @Last_user_scan
  END
  DEALLOCATE curDBIndexes
 
  -- Reconstruction des indexes
  DECLARE @DB SYSNAME,
      @Tables SYSNAME,
      @IndexName SYSNAME,
      @FillFactors VARCHAR(5),
      @IsPadded BIT,
      @RebuildCommand VARCHAR(1024)
 
  DECLARE CUR_INDEXES_TO_REBUILD CURSOR FOR
    SELECT DB,
        Tables,
        IndexName,
        CAST(
            CASE FillFactors
              WHEN 0 THEN 99
              ELSE FillFactors
            END AS VARCHAR(5)
          ),
        IsPadded
    FROM @Tbindexes
  FOR READ ONLY
 
  OPEN CUR_INDEXES_TO_REBUILD
  FETCH NEXT FROM CUR_INDEXES_TO_REBUILD INTO @DB, @Tables, @IndexName, @FillFactors, @IsPadded
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @RebuildCommand = 'ALTER INDEX ' + @IndexName + ' ON ' + @DB + '.dbo.' + @Tables +  
                  ' REBUILD WITH (FILLFACTOR = ' + @FillFactors +  
                  CASE @IsPadded
                    WHEN 1 THEN ', PAD_INDEX = ON)'
                    ELSE ')'
                  END
   
    EXEC (@RebuildCommand)
    FETCH NEXT FROM CUR_INDEXES_TO_REBUILD INTO @DB, @Tables, @IndexName, @FillFactors, @IsPadded
  END
  DEALLOCATE CUR_INDEXES_TO_REBUILD
END