Technical Article

DB Reindex all Databases

,

This script reindexes all tables in all databases.  Execute the script with the desired fill factor and it will do the rest.  There is an option to exclude databases from the reindex. This makes it easy to reindex all newly created databases. Great for the Development environment when Developers are creating database all the time.

CREATE PROCEDURE SP_RebuildAllIndexes (
  @fillfactor tinyint = null
)
AS

Set Nocount on
Declare db Cursor For--Cursor that holds the names of the databases without Pubs and Northwind
Select name from master.dbo.sysdatabases
Where name not in ('master','TempDB')
Declare @dbname varchar(100)
Declare @dbre varchar(1000)
DECLARE @execstr nvarchar(255)
Open db
Fetch Next from db into @dbname
While @@Fetch_status=0
   begin
if @dbname is null 
  Begin
       Print 'null Value'
  end
else 
  Begin
    PRINT '****************************************************************************************************** '
            PRINT 'Reindexing All Tables in ' +@dbname
      IF @fillfactor IS NULL
                SELECT @execstr = 'EXEC ' + @dbname + '..sp_MSforeachtable @command1="print ''?'' DBCC DBREINDEX (''?'')"'
            ELSE
                SELECT @execstr = 'EXEC ' + @dbname + '..sp_MSforeachtable @command1="print ''?'' DBCC DBREINDEX (''?'','''',' + str(@fillfactor) + ')"'
            EXEC(@execstr)
    PRINT ''
          End
     Fetch Next from db into @dbname
   end
Close db
Deallocate db
GO

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating