if exists (select * from sysobjects where id = object_id('dbo.utl_RebuildIdxs') and sysstat & 0xf = 4)
	drop procedure dbo.utl_RebuildIdxs
GO

create procedure utl_RebuildIdxs @objname varchar(30) 		/* the table to check for indexes */
                                 
as
set nocount on
declare @objid int		/* the object id of the table */
declare @indid int		/* the index id of an index */
declare @maxindid int
declare @DropStatement varchar(255)
declare @CreateStatement varchar(255)
declare @dbname varchar(30)
declare @indName varchar(30)	/* the index name */
declare @keys varchar(30)	/* the index key(s) */
declare @indClust int   	/* these variables refer to the attributes of the index */
declare @indIgnoreDupKey int
declare @indUnique int
declare @indIgnoreDupRow int
declare @indAllowDupRow int
declare @indUKeyConstraint int
declare @indPK int
declare @iError int
select @iError = 0
/*	Check to see that the object names are local to the current database.	*/
if @objname like '%.%.%' and
	substring(@objname, 1, charindex('.', @objname) - 1) <> db_name()
	begin
		raiserror(15250,-1,-1)
		return (1)
	end
/*	Check to see the the table exists and initialize @objid.	*/
select @objid = object_id(@objname)
/*	Table doesn't exist so return.	*/
 
if @objid is NULL
	begin
		select @dbname=db_name()
		raiserror(15009,-1,-1,@objname,@dbname)
		return (1)
	end

select @dbname = db_name()

/*	See if the object has any indexes.
**  Since there may be more than one entry in sysindexes for the object,
**  this select will set @indid to the index id of the first index.
*/
select @indid = min(indid) from sysindexes
	where id = @objid
		and indid > 1		--we only want to deal with nonclustered indexes in this proc
										--besides, we don't have any clustered indexes
		and indid < 255
select @maxindid = max(indid) from sysindexes
	where id = @objid
	and indid > 1
	and indid < 255
select @maxindid = @maxindid + 1	--this will tell us when to stop looking for indexes
		
/*	If no indexes, return.	*/
if @indid is NULL
	begin
		print 'Object does not have any indexes.'
		return (0)
	end
/*
**  Now check out each index, figure out its type and keys and
**  generate statements to drop and rebuild the indexes
*/
while @indid IS NOT NULL
begin
	
	/*	First figure out what the keys are.	*/
	declare @i int
	declare @thiskey varchar(30)
	declare @lastindid int
	select @i = 1   
	while @i <= 16
	begin
		select @thiskey = index_col(@objname, @indid, @i)
		if @thiskey is null
			goto keysdone
		if @i = 1
			select @keys = index_col(@objname, @indid, @i)
		else
			select @keys = @keys+ ', '+index_col(@objname, @indid, @i)
	
	/*	Increment @i so it will check for the next key.	*/
		
		select @i = @i + 1
	end
	/*	When we get here we now have all the keys.	*/
	keysdone:
	/*	Initialize the index description by figuring out if it's a
			clustered or nonclustered index.	*/
	select @indclust = 0		--actually this isn't necessary, since we are only dealing
	if @indid = 1						--with nonclustered indexes here
		select @indclust = 1	
	
	
	/*	Now check out the status bits for this index and set flags	*/
	/*	See if the index is ignore_dupkey (0x01).	*/
	select @indignoredupkey = 0
	if exists (select *
		from master.dbo.spt_values v, sysindexes i
		where i.status & v.number = v.number
			and v.type = 'I'
			and v.number = 1
			and i.id = @objid
			and i.indid = @indid)
	select @indignoredupkey = 1
			/*	See if the index is unique (0x02).	*/
	select @indunique = 0
	if exists (select *
		from master.dbo.spt_values v, sysindexes i
		where i.status & v.number = v.number
			and v.type = 'I'
			and v.number = 2
			and i.id = @objid
			and i.indid = @indid)
	select @indunique = 1
	/* See if the index is ignore_dup_row (0x04).	*/
	select @indignoreduprow = 0
	if exists (select *
		from master.dbo.spt_values v, sysindexes i
		where i.status & v.number = v.number
			and v.type = 'I'
			and v.number = 4
			and i.id = @objid
			and i.indid = @indid)
		select @indignoreduprow = 1
	/*	See if the index is allow_dup_row (0x40).	*/
	select @indallowduprow = 0
	if exists (select *
		from master.dbo.spt_values v, sysindexes i
		where i.status & v.number = v.number
			and v.type = 'I'
			and v.number = 64
			and i.id = @objid
			and i.indid = @indid)
		select @indallowduprow = 1
	/*	See if the index is primary key constraint (0x800).	*/
	select @indpk = 0
	if exists (select *
		from master.dbo.spt_values v, sysindexes i
		where i.status & v.number = v.number
			and v.type = 'I'
			and v.number = 2048
 			and i.id = @objid
 			and i.indid = @indid)
		select @indpk = 1
	/*	See if the index is unique key constraint (0x1000).	*/
	select @indUKeyConstraint = 0
	if exists (select * from master.dbo.spt_values v, sysindexes i
			where i.status & v.number = v.number
			and v.type = 'I'
			and v.number = 4096
			and i.id = @objid
 			and i.indid = @indid)
		select @indUKeyConstraint = 1
	
	/*	Now we have the whole description for the index so we'll 
			build and execute the drop/rebuild statements	*/
	
	
	select @indName = (select name from sysindexes where id = @objid and indid = @indid)
	select @DropStatement = case when (@indPK = 1 or @indunique = 1) then "ALTER TABLE dbo." + @objname + " WITH NOCHECK DROP CONSTRAINT " + @indName
                        	else "DROP INDEX " + @objname + "." + @indName end
	
	select @CreateStatement = case when (@indPK = 1 or @indunique = 1) then
				"ALTER TABLE dbo." + @objname + " WITH NOCHECK ADD CONSTRAINT " + @indName + 
					(CASE when @indPK = 1 then
					" PRIMARY KEY" else " UNIQUE"  END) 
				+ " NONCLUSTERED (" + @keys + ")" ELSE
				"CREATE INDEX " + @indName + " ON dbo." + @objname + "(" + @keys + ")" END
	
		
		--exec (@DropStatement)   UNCOMMENT THESE TO ACTUALLY REBUILD THE INDEXES
		--exec (@CreateStatement)
		select 'Drop Statement:', @DropStatement
		select 'CreateStatement:', @CreateStatement
		-- Trap for any SQL errors
		select @iError = @@error + @iError
    If @iError = 0
	   	begin
        --select 'index ' + @indName + ' was successfully rebuilt.'
	select 'index ' + @indName + ' was analyzed.'
			end
		else
			begin
				--select 'something is wrong.'
				return @iError
			end
	/*	Now move @indid to the next index (stop after the last one).	*/
	select @lastindid = @indid
	select @indid = NULL
	select @indid = min(indid)
		from sysindexes
		where id = @objid
			and indid > @lastindid
			and indid < @maxindid
end

return (0)
 

GO

GRANT  EXECUTE  ON dbo.utl_RebuildIdxs  TO public
GO