/*
Workfile:	REBUILD_INDEX
Created:	08/27/2001
Author:		BARB DORNBROOK
DESCRIPTION:	THIS STORED PROCEDURE WILL DROP AND REBUILD ALL 
INDEXES THAT DO NOT HAVE A PRIMARY KEY CONSTRAINT ON THEM
*/

CREATE PROC REBUILD_INDEXES
as

SET NOCOUNT ON

BEGIN


declare			@empty 	varchar(1) select @empty = ''
declare			@objectid 	int,			
			@indid 		smallint,	
			@groupid 	smallint, 
			@groupname 	sysname,
			@status 	int,
			@keys 		nvarchar(2078),
			@objname 	nvarchar(776),
			@description 	nvarchar(210), 
			@misc		nvarchar(210),
			@des1 		varchar(35),	
			@des2		varchar(35),
			@des4		varchar(35),
			@des32	varchar(35),
			@des64	varchar(35),
			@des2048 	varchar(35),
			@des4096 	varchar(35),
			@des8388608	varchar(35),
			@des16777216	varchar(35),
			@index_name 	varchar(70),
			@drop_index   	varchar(3000),
			@create_index   varchar(3000)
		
		/* FIND THE VALUE OF THE INDEX ( CLUSTERED, UNIQUE..) */
		select @des1 = name from master.dbo.spt_values where type = 'I' and number = 1
		select @des2 = name from master.dbo.spt_values where type = 'I' and number = 2
		select @des4 = name from master.dbo.spt_values where type = 'I' and number = 4
		select @des32 = name from master.dbo.spt_values where type = 'I' and number = 32
		select @des64 = name from master.dbo.spt_values where type = 'I' and number = 64
		select @des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048
		select @des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096
		select @des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608
		select @des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216

	/* CREATE TEMPORARY TABLE */
	create table #Index_Values
	(
	index_name	sysname  NULL,
	stats		int,
	groupname	sysname  NULL,
	index_keys	nvarchar(2078)	 NULL,
	objname	nvarchar(776)  NULL,
	indid		smallint,
	description	nvarchar(210) NULL,
	misc		varchar(50) NULL
	)

	/* FIND THE TABLES  */
	DECLARE object_cursor CURSOR FOR
			select id
			from sysobjects 
			where type = 'U'

	OPEN object_cursor 

      		FETCH NEXT FROM object_cursor INTO @objectid
	 	/* FIND THE TABLES WITH INDEXES */
		WHILE @@FETCH_STATUS = 0
		BEGIN
			declare index_cursor insensitive cursor for
			select indid, groupid, name, status from sysindexes
				where id = @objectid and indid > 0 and indid < 255 
				order by indid
			OPEN index_cursor
			FETCH index_cursor into @indid, @groupid, @index_name, @status

			/* NOW CHECK OUT EACH INDEX FIGURE OUT ITS KEYS AND SAVE THE INFO IN  
		   	A TEMPORARY TABLE */

			WHILE @@fetch_status >= 0
			BEGIN

				select @objname = object_name(@objectid)

				/* FIRST WE'LL FIGURE OUT WHAT THE KEYS ARE */
				declare @i int, @thiskey sysname
				select @keys = index_col(@objname, @indid, 1),
					@i = 2, @thiskey = index_col(@objname, @indid, 2)
				WHILE (@thiskey is not null )
				BEGIN
					select @keys = @keys + ', ' + @thiskey, @i = @i + 1
					select @thiskey = index_col(@objname, @indid, @i)
				END

				select @groupname = groupname from sysfilegroups where groupid = @groupid
	
				
				select	@description = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
					  case when (@status & 2)<>0 then ' '+@des2 else @empty end
					+ case when (@status & 4)<>0 then ' '+@des4 else @empty end
					+ case when (@status & 64)<>0 then ' '+@des64 else case when (@status & 32)<>0 then '  '+@des32 else @empty end end
					+ case when (@status & 2048)<>0 then ' '+@des2048 else @empty end
					+ case when (@status & 4096)<>0 then ' '+@des4096 else @empty end
					+ case when (@status & 8388608)<>0 then ' '+@des8388608 else @empty end
					+ case when (@status & 16777216)<>0 then ' '+@des16777216 else @empty end
					+ case when (@status & 16)<>0 then ' clustered' else ' nonclustered' end)

				select	@misc = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group	
					 case when (@status & 1)<>0 then ' '+@des1 else @empty end)

                                                     select @description = rtrim(ltrim(@description))

				/* INSERT ROW FOR INDEX */
				If rtrim(@description) =  'nonclustered'  OR rtrim(@description) = 'unique nonclustered' OR rtrim(@description) = 'unique clustered' OR rtrim(@description) = 'clustered'
					BEGIN
						insert into #Index_Values (index_name, stats, groupname, index_keys, objname, indid, description)
			 			values (@index_name, @status, @groupname, @keys, @objname, @indid, @description)
						If @misc <> ''
							BEGIN
								update #index_values set misc = 'WITH IGNORE_DUP_KEY'
								WHERE index_name = @index_name

							END
						
				END
				
				/* NEXT INDEX */
				fetch index_cursor into @indid, @groupid, @index_name, @status
			END

		deallocate index_cursor

		FETCH NEXT FROM object_cursor INTO @objectid

		END

	DEALLOCATE object_cursor 

/* DROP INDEXES AND GENERATE THE CREATE INDEX STATEMENT */

	declare drop_object_cursor insensitive cursor

	for Select description, index_name, objname, index_keys, groupname, misc
	From #Index_Values

	open drop_object_cursor
	fetch next from drop_object_cursor into @description, @index_name, @objname, @keys, @groupname, @misc

	WHILE @@fetch_status = 0
	BEGIN

		select @drop_index = 'drop index ' + @objname + '.' + @index_name 

		select @create_index = 'Create ' + 
					@description + 
					' index ' + 
					@index_name + 
					' on dbo.' + 
					@objname + 
					'(' + @keys + ')' +isnull( @misc,'') + ' on [' + @groupname + ']'
		
		
		select @drop_index
		exec (@drop_index)
		
		select @create_index
		exec (@create_index)

		fetch next from drop_object_cursor into @description, @index_name, @objname, @keys, @groupname, @misc

	END

	close drop_object_cursor
	deallocate drop_object_cursor
END