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