Rebuild index script

After the migration we need to rebuild the indexes:


Rebuild index script:
=================

DECLARE @Database VARCHAR(255)  

DECLARE @Table VARCHAR(255) 

DECLARE @cmd NVARCHAR(500) 

DECLARE @fillfactor INT

SET @fillfactor = 100

DECLARE DatabaseCursor CURSOR FOR 

SELECT name FROM master.dbo.sysdatabases WHERE name = 'Supernova'

OPEN DatabaseCursor 

FETCH NEXT FROM DatabaseCursor INTO @Database 

WHILE @@FETCH_STATUS = 0 

BEGIN 

   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 

   EXEC (@cmd) 

   OPEN TableCursor  

   FETCH NEXT FROM TableCursor INTO @Table  

   WHILE @@FETCH_STATUS = 0  

   BEGIN  

       BEGIN

           -- SQL 2005 or higher command

           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

PRINT (@cmd)

EXEC (@cmd)

       END

       FETCH NEXT FROM TableCursor INTO @Table  

   END  

   CLOSE TableCursor  

   DEALLOCATE TableCursor 

   FETCH NEXT FROM DatabaseCursor INTO @Database 

END 

CLOSE DatabaseCursor  

DEALLOCATE DatabaseCursor 


=======================================================================================================================================