[Resolved] How to rebuild index all table and all database - GoDevStack.com

 How to rebuild index all table and all database - GoDevStack.com

 

How to rebuild index all table and all database - GoDevStack.com

[Solved]

DECLARE @ListDatabase NVARCHAR(255)   

DECLARE @ListTable NVARCHAR(255)  

DECLARE @query NVARCHAR(1000)  

DECLARE DatabaseCursor CURSOR READ_ONLY FOR  

SELECT name FROM master.sys.databases   

WHERE name NOT IN ('master','msdb','tempdb','model','distribution')  -- databases to exclude

--WHERE name IN ('GoDevStackDB1', 'GoDevStackDB2') -- use this to select specific databases and comment out line above

AND state = 0 -- database is online

AND is_in_standby = 0 -- database is not read only for log shipping

ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @ListDatabase  

WHILE @@FETCH_STATUS = 0  

BEGIN  

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

   table_name + '']'' as tableName FROM [' + @ListDatabase + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''   

   -- create table cursor  

   EXEC (@query)  

   OPEN TableCursor   

   FETCH NEXT FROM TableCursor INTO @ListTable   

   WHILE @@FETCH_STATUS = 0   

   BEGIN

      BEGIN TRY   

         SET @query = 'ALTER INDEX ALL ON ' + @ListTable + ' REBUILD' 

         --PRINT @query -- uncomment if you want to see commands

         EXEC (@query) 

      END TRY

      BEGIN CATCH

         PRINT '---'

         PRINT @query

         PRINT ERROR_MESSAGE() 

         PRINT '---'

      END CATCH

      FETCH NEXT FROM TableCursor INTO @ListTable   

   END   

   CLOSE TableCursor   

   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @ListDatabase  

END  

CLOSE DatabaseCursor   

DEALLOCATE DatabaseCursor

I hope it can help,

GoDevStack.com