Stored Procedure to Reindex All Indexes With All DBs or Single DB

				
--**************************************
-- Name: Stored Procedure to Reindex All Indexes With All DBs or Single DB
-- Description:The purpose of this procedure is to reindex all indexes in any given or all databases.
-- By: James Travis
--
-- Inputs:Procedure Name with either no database name for only variable or enter a specific variable.
--
-- Returns:Will output status from DBCC DBREINDEX process.
--
-- Assumes:I have seen a few examples for reindex databases on servers all of which are based on the Microsoft example. I noticed thou none have included dealing with database tables not owned by dbo and thus those tables do not get reindexed. Also I wanted to be able to reindex all the databases on my server every few months without having to split the process up and decided I would cursor thru all the database namse which are stored in the sysdatabases table in master. Thus I can fire and forget for the most part.
--
-- Side Effects:FIrst there have been issues reported with using DBCC REINDEX and you will want to review the information from Microsofts Knowledge Base, as well as, test on a non-production server first. Will degrade server performance while running and can lock tables during reindex process making them unavailable until complete with that table being reindexed. Afterwards can and should show improvement on queries do to reorganizing non-clustered indexes.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=302&lngWId=5--for details.--**************************************

ALTER PROCEDURE sp_RebuildIndexes
@ReIndexMe VARCHAR(500) = NULL --Supply a database name, if not the value will default to NULL
AS
DECLARE @DBName VARCHAR(500), @SQLState VARCHAR(5000) --Primary Variables 
--Things to Note:
--TableCursor select selects DatabaseName.TableSchema.TableName .
--The reason is to insure all tables are reindexed including those that are not owned by dbo just in case someone does this.
--Also where code reads DBCC DBREINDEX(@TableName,'' '',90) I am rebuilding with a fill factor or 90%, you may want to change
--or you could make as an additional parameter for input, or even create another table on the database that it can utilize to store individual
--fill factors. This is all up to you.
IF @ReIndexMe IS NOT NULL --If the DatabaseName is supplied reindex only that database
	BEGIN
		SET @DBName = @ReIndexMe
		SET @SQLState = '
		DECLARE TableCursor CURSOR FOR SELECT ''' + @DBName + '.'' + TABLE_SCHEMA + ''.'' + table_name FROM [' + @DBName + '].information_schema.tables WHERE table_type = ''base table''
		DECLARE @TableName varchar(255)
		OPEN TableCursor
		FETCH NEXT FROM TableCursor INTO @TableName
		WHILE @@FETCH_STATUS = 0
		BEGIN 
		SELECT ''Reindexing '' + @TableName as DOING
		DBCC DBREINDEX(@TableName,'' '',90)
		FETCH NEXT FROM TableCursor INTO @TableName
		END
		CLOSE TableCursor
		DEALLOCATE TableCursor'
		EXEC (@SQLSTate)
	END
ELSE	--If the DatabaseName is not supplied Reindex all Databases on server
	BEGIN
		DECLARE DBCursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases --Get All Database Names and Load into Cursor. 
			--Also if you do not wish to reindex the Master, model and msdb databases or any others add a WHERE name not in (list of databases) to prevent
		OPEN DBCursor
		FETCH NEXT FROM DBCursor INTO @DBName
		WHILE @@FETCH_STATUS = 0
		BEGIN
		SET @SQLState = '
		DECLARE TableCursor CURSOR FOR SELECT ''' + @DBName + '.'' + TABLE_SCHEMA + ''.'' + table_name FROM [' + @DBName + '].information_schema.tables WHERE table_type = ''base table''
		DECLARE @TableName varchar(255)
		OPEN TableCursor
		FETCH NEXT FROM TableCursor INTO @TableName
		WHILE @@FETCH_STATUS = 0
		BEGIN 
		SELECT ''Reindexing '' + @TableName as DOING
		DBCC DBREINDEX(@TableName,'' '',90)
		FETCH NEXT FROM TableCursor INTO @TableName
		END
		CLOSE TableCursor
		DEALLOCATE TableCursor'
		EXEC (@SQLSTate)
		FETCH NEXT FROM DBCursor INTO @DBName
		END
		CLOSE DBCursor
		DEALLOCATE DBCursor
	END