REBUILD all the index existing in Database

You can run the codes by Stored procedure also.

DECLARE @Table VARCHAR(255)  

DECLARE @fillfactor INT

DECLARE @cmd NVARCHAR(500) 

SET @fillfactor = 90

  

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName FROM 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  

-- SQL 2005 or higher command

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

           EXEC (@cmd)

--      

       FETCH NEXT FROM TableCursor INTO @Table  

   END  

   CLOSE TableCursor  

   DEALLOCATE TableCursor

   EXEC sp_updatestats

Code explanation:

1. We have created a cursor called "TableCursor" which is running through a  "@cmd" variable.

2. Now we have to loop the cursor until @@FETCH_STATUS = 0.

3. @@FETCH_STATUS = 0 means the entire table values has read.

4. Now alter all existing index in a table and make REBUILD.

5. At last we have to run  sp_updatestats sp to configure statistics of indexes.