CREATE PROCEDURE dbo.usp_Reindex
@Owner	Sysname = NULL,
@Fillfactor	Int  = 90
AS

/*====================================================================================================
NAME: 	usp_Reindex						CREATED BY: SD
TYPE: 	stored procedure (SQL 7)					CREATION DATE: 08/29/01

USAGE:
DECLARE @Owner	Sysname
DECLARE @Fillfactor	Int

SET @Fillfactor = 85
Exec usp_Reindex @Owner, @Fillfactor

PURPOSE	: To script the DBCC Reindex statement for all the Indexes in the 
database.


Notes :-
1) DBCC DBReindex puts locks on the table. Therefore execute it only when 
the database activity is very less.
I would recommend putting the Database in 'dbo use only' before Reindexing 
the tables.
2) Fillfactor can be any value between 0 & 100.
3) Using fillfactor avoids page splitting.
4) For more information on fillfactor, see BOL.


Validations Performed :-
1) Checks for valid value of Fillfactor i.e. between 0 & 100.
2) Checks for Tables without any Indexes.
3) Takes into account the Owner of the tables.

====================================================================================================*/
/*___________________________________________________________________________________________________*/

SET NOCOUNT ON

/* Check if the Value of Fillfactor is Valid	*/
IF @Fillfactor NOT BETWEEN 0 AND 100
BEGIN
	Raiserror('Invalid Fillfactor specified',16,1) with seterror
	Return(@@Error)
END

/* Generate the statements for Script	*/
SELECT DISTINCT 'DBCC DBREINDEX([' + su.name + '.' + so.name + '],[' + 
si.name + '],' + Convert(varchar(3),@Fillfactor) + ')'
FROM sysobjects so INNER JOIN sysindexkeys sk
ON so.id = sk.id
INNER JOIN sysindexes si
ON sk.id = si.id
AND sk.indid = si.indid
INNER JOIN sysusers su
ON so.uid = su.uid
WHERE so.xtype = 'U'
AND (@Owner IS NULL OR su.name = @Owner)

SET NOCOUNT OFF
Go