Friday, 15 February 2013

Reorganize indexes for all tables in all user databases

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 

No comments:

Post a Comment