[SQL Server] Index nouvelle réorganisation

DECLARE @Database VARCHAR(255)

DECLARE @Table VARCHAR(255)

DECLARE @cmd NVARCHAR(500)

DECLARE @readOnly BIT;

DECLARE @timeStart DATETIME;

DECLARE @timeEND DATETIME;

DECLARE DatabaseCursor CURSOR FOR

  SELECT name,

         is_read_only

  FROM   master.sys.databases

  WHERE  database_id > 4 -- Only user databases

  ORDER  BY name

 

 OPEN DatabaseCursor

 

FETCH NEXT FROM DatabaseCursor INTO @Database, @readOnly

 

WHILE @@FETCH_STATUS = 0

  BEGIN

      -- turn off read-only if read only

      IF @readOnly = 1

        BEGIN

            SET @cmd = 'ALTER DATABASE ' + @Database

                       + ' SET READ_WRITE WITH NO_WAIT;'

 

            EXEC(@cmd);

        END

 

      SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +

  table_name + '']'' as tableName FROM ['

                 + @Database

                 + '].INFORMATION_SCHEMA.TABLES

  WHERE table_type = ''BASE TABLE'''

      -- create table cursor 

      SET @timeStart = Getdate();

 

      PRINT 'START REORGANIZE ALL INDEXES FOR  '

            + @Database + ':'

            + CONVERT(VARCHAR(24), @timeStart, 121);

 

      EXEC (@cmd)

 

      OPEN TableCursor

 

      FETCH NEXT FROM TableCursor INTO @Table

 

      WHILE @@FETCH_STATUS = 0

        BEGIN

            SET @cmd = 'ALTER INDEX ALL ON ' + @Table

                       + ' REORGANIZE;'

 

            EXEC (@cmd)

 

            FETCH NEXT FROM TableCursor INTO @Table

        END

 

      CLOSE TableCursor

 

      DEALLOCATE TableCursor

 

      SET @timeEnd = Getdate();

 

      PRINT 'END REORGANIZE ALL INDEXES FOR  '

            + @Database + ':'

            + CONVERT(VARCHAR(24), @timeStart, 121);

 

      PRINT 'TIME TAKEN FOR ' + @Database + ' = '

            + CONVERT(VARCHAR(100), Datediff(ss, @timeStart, @timeEnd))

            + 'secs';

 

      -- set DB back to read-only if read only

      IF @readOnly = 1

        BEGIN

            SET @cmd = 'ALTER DATABASE ' + @Database

                       + ' SET READ_ONLY WITH NO_WAIT;'

 

            EXEC(@cmd);

        END

 

      FETCH NEXT FROM DatabaseCursor INTO @Database, @readOnly

  END

 

CLOSE DatabaseCursor

 

DEALLOCATE DatabaseCursor