Smart Index Manipulation system

,

We are constantly having to drop indexes
from tables while repopulating the data
in the table. We then have to rebuild the
indexes as they were before dropping them.
This requires writing a custom drop and recreate index
script for each unique situation. If we try
to write scripts ahead of time as soon as the indexing scheme
changes on the table the scripts must be changed as well.
This sp is part of a collection of five stored
procedures I call the Smart Index Manipulation System. It allows you to drop indexes dynamically and then recreate them later just as they were before the drop. You can also keep a running history of your indexes as they change over time.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_LogIndexes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_LogIndexes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RebuildSavedIndexes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_RebuildSavedIndexes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_SavenDropIndexes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SavenDropIndexes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_SearchIndexHist]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SearchIndexHist]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_compare_IndexHist_to_existing]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_compare_IndexHist_to_existing]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO















CREATE       procedure sp_LogIndexes (@table varchar(55) = null, @clustereduq

varchar(1) = 'N', @WhenDescrip varchar(50) = 'Before Import') as

/******************************

This sp is part of a collection of five stored
procedures I call the Smart Index Manipulation System.
This sp records the existing index settings on the table into
the tbl_IndexList table. The
part of the code that dynamically creates the drop
statement is based on a stored procedure I found
on the InterNet. Author : Eddy Djaja, Publix Super Markets, Inc.
Revision: 12/07/1999 born date
The Smart Index Manipulation system was co-developed 
with my co-worker Dale Butcher.

Phillip D. Snipes
7/22/02

Dale Butcher
7/11/02
*******************************/

 

 

set nocount on

 

 

 

/* process */

 

 

--clean table

--     

-- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY

 

declare @empty varchar(1) 

select @empty = ''

declare @des1              varchar(35),       -- 35 matches spt_values

            @des2              varchar(35),

            @des4              varchar(35),

            @des32                        varchar(35),

            @des64                        varchar(35),

            @des2048         varchar(35),

            @des4096         varchar(35),

            @des8388608   varchar(35),

            @des16777216  varchar(35),

            @objectid          Int,                                

            @indid              smallint,            

            @groupid          smallint, 

            @groupname     sysname,

            @status            Int,

            @keys              nvarchar(2078),

            @objname         nvarchar(776),

            @description     nvarchar(210), 

            @misc              nvarchar(210),

            @index_name   varchar(70),

            @table_there1 char(5),

            @table_there2 char(5),

            @Now               Datetime,

            @i                    Int, 

            @thiskey          sysname,

            @sql                 varchar(100),

            @trgsql             varchar(1555)

 

Select @Now = getdate()

 

Set @table_there1 = 'False'

if exists (select * from dbo.sysobjects where id =

object_id(N'[dbo].[tbl_IndexList]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

            Set @table_there1 = 'True'

 

If @table_there1 = 'False'

            BEGIN TRAN T1


                        CREATE TABLE [dbo].[tbl_IndexList] (

                        [TableName] [varchar] (255) NULL ,

                        [index_name] [sysname] NULL ,

                        [stats] [int] NULL ,

                        [groupname] [sysname] NULL ,

                        [index_keys] [nvarchar] (2078) NULL ,

                        [indid] [smallint] NULL ,

                        [indexdescription] [nvarchar] (210) NULL ,

                        [misc] [varchar] (50) NULL ,

                        [WhenDescription] [varchar] (50) NULL ,

                        [SnapshotTime] [datetime] NOT NULL 

)

           COMMIT TRAN T1

 

Set @table_there2 = 'False'

if exists (select * from dbo.sysobjects where id =

object_id(N'[dbo].[tbl_IndexListHistory]') and OBJECTPROPERTY(id, N'IsUserTable') =

1)

            Set @table_there2 = 'True'

 

If @table_there2 = 'False'

            BEGIN TRAN T2

                        CREATE TABLE [dbo].[tbl_IndexListHistory] (

                        [TableName] [varchar] (255) NULL ,

                        [index_name] [sysname] NULL ,

                        [stats] [int] NULL ,

                        [groupname] [sysname] NULL ,

                        [index_keys] [nvarchar] (2078) NULL ,

                        [indid] [smallint] NULL ,

                        [indexdescription] [nvarchar] (210) NULL ,

                        [misc] [varchar] (50) NULL ,

                        [WhenDescription] [varchar] (50) NULL ,

                        [SnapshotTime] [datetime] NOT NULL 

)

            COMMIT TRAN T2

 

 

 

--table insert  *****SUPPORTS UP TO 21 INDEXED COLUMNS.  MODIFY FOR MORE, IF

--NEC.******

If @clustereduq = 'N'

            Begin

                        /* 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
 

 

/* FIND THE TABLES  */

                        Declare object_cursor CURSOR For

                        Select id

                        from sysobjects 

                        where name = @table

                        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  

                                    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 & 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 tbl_IndexList (tablename, index_name, stats, groupname, index_keys, indid, indexdescription, whendescription, snapshottime)
                                                        values (@objname, @index_name, @status, @groupname, @keys, @indid, @description, @WhenDescrip, @Now)

                                                            If @misc <> ''
                                                                        Begin
	                                                                        update tbl_IndexList
											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

            --Remove Clustered Unique Indexes from list if @clustereduq set to N(default)

            Delete from tbl_IndexList where TableName = @table and IndexDescription = 'unique clustered'

            End 

 

 

If @clustereduq = 'Y'

            Begin

                        /* 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
 

 

/* FIND THE TABLES  */

                        Declare object_cursor CURSOR For

                        Select id

                        from sysobjects 

                        where name = @table

                        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  

                                    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 & 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 tbl_IndexList (tablename, index_name, stats, groupname, index_keys, indid, indexdescription, whendescription, snapshottime)
                                                        values (@objname, @index_name, @status, @groupname, @keys, @indid, @description, @WhenDescrip, @Now)

                                                            If @misc <> ''
                                                                        Begin
	                                                                        update tbl_IndexList
											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
	End


If @table_there1 = 'False'


	Begin
		Set @trgsql = 'create trigger trg_del_indexhistory
on tbl_indexlist
for delete
as 
begin


insert into tbl_indexlisthistory(tablename, index_name, stats, groupname, index_keys, indid, indexdescription, misc, whendescription, snapshottime)
select tablename, index_name, stats, groupname, index_keys, indid, indexdescription, misc, whendescription, snapshottime
	from deleted

end'
	End
        BEGIN TRAN TR1
		Exec (@trgsql)
	COMMIT TRAN TR1

         





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO










CREATE    proc sp_RebuildSavedIndexes (@table varchar(55))
as

/******************************

We are constantly having to drop indexes
from tables while repopulating the data
in the table. We then have to rebuild the 
indexes as they were before dropping them.
This requires writing a custom drop and recreate index 
script for each unique situation. If we try
to write scripts ahead of time as soon as the indexing scheme
changes on the table the scripts must be changed as well.
This sp is part of a collection of five stored
procedures I call the Smart Index Manipulation System.
This sp cursors through the tbl_IndexList table
to read the recorded index values necessary for
dynamically building the create index statements. The
part of the code that dynamically builds the creates 
statement is based on a stored procedure I found
on the InterNet. Author : Eddy Djaja, Publix Super Markets, Inc.
Revision: 12/07/1999 born date
The last thing that occurs is a delete on the
tbl_IndexList table to remove the records for the 
table you are currently rebuilding. There is a delete trigger
on this table which will write the deleted records out
to the tbl_IndexListHistory table. This is so that you have a 
running history of Index Values on your tables past a 
present. The tbl_IndexHistory table
was Dale's idea. This is part of the reason I call this the
Smart Index Manipulation system. It was co-developed 
with my co-worker Dale Butcher.

Phillip D. Snipes
7/25/02

Dale Butcher
7/21/02
*******************************/

/*********************
Print help message if no search criteria specified.
**********************/
     if @table = '' 

	Begin  

    		Print '#################################################' 

   		Print char(13)+'You must pass a table name on which to recreate indexes.' 

		Print char(13)+'exec sp_RebuildSavedIndexes Authors' 

    		Print char(13)+'#################################################' 

  		Return


		End 

Declare			@objectid 	Int,			
			@indid 		smallint,	
			@groupid 	smallint, 
			@groupname 	sysname,
			@status 	Int,
			@keys 		nvarchar(2078),
			@objname 	nvarchar(776),
			@description 	nvarchar(210), 
			@misc		nvarchar(210),
			@index_name 	varchar(70),
			@createindexsql varchar(512),
			@errorstr	varchar(105)
Set NOCOUNT On
set @createindexsql = ''
set @errorstr = ''


if exists (select 1 from tbl_IndexList where tablename = @table)
	BEGIN
		Declare create_object_cursor insensitive cursor
		For Select tablename, indexdescription, index_name, index_keys, groupname, misc
		From tbl_IndexList
		where tablename = @table
		Open create_object_cursor
		fetch Next from create_object_cursor into @objname, @description, @index_name, @keys, @groupname, @misc
		While @@fetch_status = 0
			Begin

				Select @createindexsql = 'Create ' + 
					@description + 
					' index ' + 
					@index_name + 
					' on dbo.' + 
					@objname + 
					'(' + @keys + ')' +isnull( @misc,'') + ' on [' + @groupname + ']'
				Select @createindexsql
				Begin
					--Select @createindexsql
					exec (@createindexsql)
				End

			fetch Next from create_object_cursor into @objname, @description, @index_name, @keys, @groupname, @misc

			End

		Close create_object_cursor
		deallocate create_object_cursor
	delete from tbl_indexlist where TableName = @table
	END
If @createindexsql = ''
Begin
	Set @errorstr =  'Error: -- There were no saved indexes to rebuild for the table ' + @table + '.'
	Select @errorstr
End
Set NOCOUNT OFF
return (@@error)











GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE         proc sp_SavenDropIndexes (@table varchar(55), @Clustereduq varchar(1) = 'N', @whenDescrip varchar(50) = 'Before Import')
as

/******************************

We are constantly having to drop indexes
from tables while repopulating the data
in the table. We then have to rebuild the 
indexes as they were before dropping them.
This requires writing a custom drop and recreate index 
script for each unique situation. If we try
to write scripts ahead of time as soon as the indexing scheme
changes on the table the scripts must be changed as well.
This sp is part of a collection of five stored
procedures I call the Smart Index Manipulation System.
This sp calls the sp_LogIndexes stored procedure to
record the existing index settings on the table into
a table before performing the drop. It builds the drop statement dynamically
based on what indexes are present at that time. The
part of the code that dynamically creates the drop
statement is based on a stored procedure I found
on the InterNet. Author : Eddy Djaja, Publix Super Markets, Inc.
Revision: 12/07/1999 born date
The Smart Index Manipulation system was co-developed 
with my co-worker Dale Butcher.

Phillip D. Snipes
7/25/02

Dale Butcher
7/21/02
*******************************/

/*********************
Print help message if no search criteria specified.
**********************/
     if @table = '' 

	Begin  

    		Print '#################################################' 

   		Print char(13)+'You must pass a table name on which to drop indexes.' 

   		Print char(13)+'Two additional optional parameters are as follows:'

		Print char(13)+'First) Clustered unique - accepts Y or N, defaults to N, If set to Yes the sp will' 

   		Print char(13)+'drop the Clustered Unique index on the table if it exists.' 

   		Print char(13)+'Second) When Description - accepts up to 50 characters, defaults to Before Import, ' 

		Print char(13)+'Short description of when or why the indexes are being dropped' 

   		Print char(13)+'example: To search the most recent previous SQL Error Log for the word kernel'

		Print char(13)+'exec sp_SavenDropIndexes Authors, Y, Before Rebuild' 

    		Print char(13)+'#################################################' 

  		Return


		End 
Declare			@objectid 	Int,			
			@indid 		smallint,	
			@groupid 	smallint, 
			@groupname 	sysname,
			@status 	Int,
			@keys 		nvarchar(2078),
			@objname 	nvarchar(776),
			@description 	nvarchar(210), 
			@misc		nvarchar(210),
			@index_name 	varchar(70),
			@dropindexsql varchar(512)
set @dropindexsql = ''



Exec sp_LogIndexes @table, @Clustereduq, @whenDescrip

	if exists (select 1 from tbl_IndexList where tablename = @table)

		begin
			Declare drop_object_cursor insensitive cursor
			For Select tablename, indexdescription, index_name, index_keys, groupname, misc
			From tbl_IndexList
			where tablename = @table
			Open drop_object_cursor
			fetch Next from drop_object_cursor into @objname, @description, @index_name, @keys, @groupname, @misc
			While @@fetch_status = 0
			Begin
				Select @dropindexsql = 'drop index ' + @objname + '.' + @index_name 

				Begin
					--Select @dropindexsql
					exec (@dropindexsql)
				End
			fetch Next from drop_object_cursor into @objname, @description, @index_name, @keys, @groupname, @misc
			End
		Close drop_object_cursor
		deallocate drop_object_cursor
		End
return (@@error)




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE     Procedure sp_SearchIndexHist (@table varchar(55) = null, @backcount int = '1', @backperiod varchar(1) = 'D')  
as
/******************************

This sp is part of a collection of five stored
procedures I call the Smart Index Manipulation System.
This sp calls is used to search the tbl_IndexListHistory
table for Index Values on a table during a certain
time frame. You would use the data returned from
this sp to help you in calling the sp_compare_IndexHist_to_existing 
sp. The resoning will be made apparent in the comment block
of the sp_compare_IndexHist_to_existing sp.
The Smart Index Manipulation system was co-developed 
with my co-worker Dale Butcher.

Phillip D. Snipes
7/29/02

*******************************/

/*********************
Print help message if no search criteria specified.
**********************/
     if @table = null 

	Begin  

    		Print '#################################################' 

   		Print char(13)+'You must pass a table name on which to search the tbl_IndexListHistory table.' 

   		Print char(13)+'Two additional optional parameters are as follows:'

		Print char(13)+'First) Back Count - accepts integers, defaults to 1, Determines date multiplier factor' 

    		Print char(13)+'Second) Back Period - accepts D (for Days), W (for Weeks), M (for Months), Y (for Years) characters, defaults to D, Determines the period type' 

   		Print char(13)+'example: To search Index History for the index values of Authors from 2 weeks agor to present submit the following:'

		Print char(13)+'exec sp_SearchIndexHist Authors, 2, W' 

    		Print char(13)+'#################################################' 

  		Return


		End 
Declare @Now datetime
Declare @BeginDate datetime
Declare @dspbgdate varchar(12)
Declare @Rows int
Declare @Output1 varchar(125)
Declare @Output2 varchar(125)

Set @Now = GetDate()

If @backperiod = 'D'
	Begin
		Set @BeginDate = (@Now - @backcount ) 
	End

If @backperiod = 'W'
	Begin
		Set @backcount = @backcount * 7
		Set @BeginDate = (@Now - @backcount )
	End

If @backperiod = 'M'
	Begin
		Set @backcount = @backcount * 30
		Set @BeginDate = (@Now - @backcount )
	End

If @backperiod = 'Y'
	Begin
		Set @backcount = @backcount * 365
		Set @BeginDate = (@Now - @backcount )
	End

Set @dspbgdate = CONVERT(varchar, @BeginDate, 101)
Set @Output1 = 'Results from the tbl_IndexListHistory table for ' + @table + ' entries from ' + @dspbgdate + ' forward. Most recent first.'

Set NOCOUNT On
Select @Output1


Set @Rows = (Select Count(*) from tbl_IndexListHistory where TableName = @table and SnapshotTime >= @BeginDate)

If @Rows = 0
	Begin
		Set @Output2 = 'There were no results returned for the search criteria you specified.'
		Select @Output2
	End
Set NOCOUNT Off
If @Rows > 0
	Begin
		Select TableName, index_name as IndexName, index_keys as Fields, groupname as FileGroup, WhenDescription, CONVERT(varchar, SnapshotTime, 101) as BeginDate, SnapshotTime as ExactTime
		From tbl_IndexListHistory
		Where TableName = @table and SnapshotTime >= @BeginDate
		Order By SnapShotTime Desc
	End





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE      Procedure sp_compare_IndexHist_to_existing (@table varchar(55) = null, @HistDate datetime, @WhenDescrip varchar(50) = 'Before Import', @OSql varchar(1) = 'N' )  
as
set nocount on
/******************************

This sp is part of a collection of five stored
procedures I call the Smart Index Manipulation System.
This sp call is used to compare the values 
recorded in the tbl_IndexListHistory
with the indexes that currently exist on 
the table. Useful for analyzing changes to
indexing over time.
The Smart Index Manipulation system was co-developed 
with my co-worker Dale Butcher.

Phillip D. Snipes
7/29/02

*******************************/

/*********************
Print help message if no search criteria specified.
**********************/
     if @HistDate = null 

	Begin  

    		Print '#################################################' 

   		Print char(13)+'You must pass a table name on which to compare against the tbl_IndexListHistory table.' 

   		Print char(13)+'Three additional optional parameters are as follows:'

		Print char(13)+'First) Historical Date - accepts date in MM/DD/YYYY format, Determines what day to compare against' 

    		Print char(13)+'Second) When Description - accepts up to 50 characters. The When description to compare against' 

		Print char(13)+'Third) Output SQL - accepts Y. Optional switch which when set to Y will also output the necessary sql to put the' 
   		
		Print char(13)+'indexes back to the way they were in the History table.' 

		Print char(13)+'example: To compare Indexes on Authors on 7/17/2002 During the Import and Output the SQL submit the following:'

		Print char(13)+'exec sp_compare_IndexHist_to_existing Authors, 7/17/2002, During the Import, Y' 

    		Print char(13)+'#################################################' 

  		Return


		End 


Declare @OutputE varchar(255)
Declare @Existing int
Declare @OutputB varchar(255)
Declare @Before int
Declare @Now datetime
Declare @Now1 int
Declare @OutputN varchar(255)
declare @empty varchar(1) 

select @empty = ''

Set @Now = (Getdate())

declare @des1              varchar(35),       -- 35 matches spt_values

            @des2              varchar(35),

            @des4              varchar(35),

            @des32                        varchar(35),

            @des64                        varchar(35),

            @des2048         varchar(35),

            @des4096         varchar(35),

            @des8388608   varchar(35),

            @des16777216  varchar(35),

            @objectid          Int,                                

            @indid              smallint,            

            @groupid          smallint, 

            @groupname     sysname,

            @status            Int,

            @keys              nvarchar(2078),

            @objname         nvarchar(776),

            @description     nvarchar(210), 

            @misc              nvarchar(210),

            @index_name   varchar(70),

            @table_there1 char(5),

            @table_there2 char(5),

            @i Int,

            @thiskey          sysname,

            @sql                 varchar(100)

CREATE TABLE #ExistingIndexes (

                        [TableName] [varchar] (255) NULL ,

                        [index_name] [sysname] NULL ,

                        [stats] [int] NULL ,

                        [groupname] [sysname] NULL ,

                        [index_keys] [nvarchar] (2078) NULL ,

                        [indid] [smallint] NULL ,

                        [indexdescription] [nvarchar] (210) NULL ,

                        [misc] [varchar] (50) NULL ,

                        [WhenDescription] [varchar] (50) NULL ,

                        [SnapshotTime] [datetime] NOT NULL 

)


Begin

                        /* 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
 

 

/* FIND THE TABLES  */

                        Declare object_cursor CURSOR For

                        Select id

                        from sysobjects 

                        where name = @table

                        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  

                                    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 & 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 #ExistingIndexes (tablename, index_name, stats, groupname, index_keys, indid, indexdescription, whendescription, snapshottime)
                                                        values (@objname, @index_name, @status, @groupname, @keys, @indid, @description, @WhenDescrip, @Now)

                                                            If @misc <> ''
                                                                        Begin
	                                                                        update #ExistingIndexes
											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
	End
CREATE TABLE #ListA (

                        [TableName] [varchar] (255) NULL ,

                        [index_name] [sysname] NULL ,

                        [stats] [int] NULL ,

                        [groupname] [sysname] NULL ,

                        [index_keys] [nvarchar] (2078) NULL ,

                        [indid] [smallint] NULL ,

                        [indexdescription] [nvarchar] (210) NULL ,

                        [misc] [varchar] (50) NULL ,

                        [WhenDescription] [varchar] (50) NULL ,

                        [SnapshotTime] [datetime] NOT NULL 

)

insert into #ListA
SELECT DISTINCT #ExistingIndexes.TableName, #ExistingIndexes.Index_Name, 
#ExistingIndexes.stats, #ExistingIndexes.groupname, 
#ExistingIndexes.index_keys, 
#ExistingIndexes.indid, 
#ExistingIndexes.indexdescription,
#ExistingIndexes.misc,  
#ExistingIndexes.WhenDescription,
#ExistingIndexes.SnapShotTime
FROM #ExistingIndexes LEFT JOIN tbl_IndexListHistory 
--ON (#ExistingIndexes.IndexDescription = tbl_IndexListHistory.IndexDescription) 
ON (#ExistingIndexes.Index_Name = tbl_IndexListHistory.Index_Name) 
AND (#ExistingIndexes.TableName = tbl_IndexListHistory.TableName)
WHERE ((tbl_IndexListHistory.TableName) Is Null)
And CONVERT(varchar, tbl_IndexListHistory.Snapshottime, 101) = @HistDate
And #ExistingIndexes.TableName = @Table
--Order By #ExistingIndexes.TableName


CREATE TABLE #ListB (

                        [TableName] [varchar] (255) NULL ,

                        [index_name] [sysname] NULL ,

                        [stats] [int] NULL ,

                        [groupname] [sysname] NULL ,

                        [index_keys] [nvarchar] (2078) NULL ,

                        [indid] [smallint] NULL ,

                        [indexdescription] [nvarchar] (210) NULL ,

                        [misc] [varchar] (50) NULL ,

                        [WhenDescription] [varchar] (50) NULL ,

                        [SnapshotTime] [datetime] NOT NULL 

)


insert into #ListB
SELECT DISTINCT tbl_IndexListHistory.TableName, tbl_IndexListHistory.Index_Name,
tbl_IndexListHistory.stats, tbl_IndexListHistory.groupname, 
tbl_IndexListHistory.index_keys, tbl_IndexListHistory.indid, 
tbl_IndexListHistory.IndexDescription,
tbl_IndexListHistory.misc, 
tbl_IndexListHistory.WhenDescription, 
tbl_IndexListHistory.SnapshotTime
FROM tbl_IndexListHistory LEFT JOIN #ExistingIndexes 
--ON (tbl_IndexListHistory.IndexDescription = #ExistingIndexes.IndexDescription) 
ON (tbl_IndexListHistory.Index_Name = #ExistingIndexes.Index_Name) 
AND (tbl_IndexListHistory.TableName = #ExistingIndexes.TableName)
WHERE ((#ExistingIndexes.TableName) Is Null)
And CONVERT(varchar, tbl_IndexListHistory.Snapshottime, 101) = @HistDate
And tbl_IndexListHistory.TableName = @table
And tbl_IndexListHistory.WhenDescription = @WhenDescrip
--Order By #ExistingIndexes.TableName
Select @Existing = (SELECT COUNT(*) FROM #ExistingIndexes)
	IF @Existing > 0
		BEGIN
			Set @OutputE = 'The folowing Indexes currently exist on ' + @table + '.'
			Select @OutputE
			Select tablename, index_name, indexdescription, index_keys, indid, stats, groupname, misc, WhenDescription, SnapShotTime from #ExistingIndexes Order By IndexDescription
			If @OSql = 'Y'
				BEGIN
					Set @OutputE = ''
					Set @OutputE = 'The folowing sql commands would drop the current indexes from ' + @table + '.'
					Select @OutputE
					Declare		@dropindexsql varchar(512)
					set @dropindexsql = ''
					Declare drop_object_cursor insensitive cursor
					For Select tablename, indexdescription, index_name, index_keys, groupname, misc
					From #ExistingIndexes
					where tablename = @table
					Open drop_object_cursor
					fetch Next from drop_object_cursor into @objname, @description, @index_name, @keys, @groupname, @misc
					While @@fetch_status = 0
					Begin
						Select @dropindexsql = 'drop index ' + @objname + '.' + @index_name 

						Begin
							Select @dropindexsql
							--exec (@dropindexsql)
						End
					fetch Next from drop_object_cursor into @objname, @description, @index_name, @keys, @groupname, @misc
					End
				Close drop_object_cursor
				deallocate drop_object_cursor
				End
					
		END
	ELSE
		BEGIN
			Set @OutputE = 'There are no Indexes that currently exist on ' + @table + '.'
			Select @OutputE
		END
	
Select @Before = (SELECT COUNT(*) FROM #ListA)
	IF @Before > 0
		BEGIN
			Set @OutputB = 'The folowing Indexes were on ' + @table + ' on the date compared but, do not exist  on ' + @table + ' now.'
			Select @OutputB
			Select Distinct tablename, index_name, indexdescription, index_keys, indid, stats, groupname, misc, WhenDescription from #ListA
			If @OSql = 'Y'
				BEGIN
					Set @OutputN = ''
					Set @OutputN = 'The folowing sql commands would re-create the missing indexes on ' + @table + '.'
					Select @OutputN
					Declare @createindexsqlA varchar(512)
					Declare create_object_cursorA insensitive cursor
					For Select Distinct tablename, indexdescription, index_name, index_keys, groupname, misc
					From #ListA
					where tablename = @table
					Open create_object_cursorA
					fetch Next from create_object_cursorA into @objname, @description, @index_name, @keys, @groupname, @misc
					While @@fetch_status = 0
					Begin

						Select @createindexsqlA = 'Create ' + 
						@description + 
						' index ' + 
						@index_name + 
						' on dbo.' + 
						@objname + 
						'(' + @keys + ')' +isnull( @misc,'') + ' on [' + @groupname + ']'
					--Select @createindexsql
						Begin
							Select @createindexsqlA
							--exec (@createindexsql)
						End

					fetch Next from create_object_cursorA into @objname, @description, @index_name, @keys, @groupname, @misc

					End

				Close create_object_cursorA
				deallocate create_object_cursorA
				END
		END

Select @Now1 = (SELECT COUNT(*) FROM #ListB)
	IF @Now1 > 0
		BEGIN
			Set @OutputN = 'The following Indexes were on ' + @table + ' on the date compared but, do not exist  on ' + @table + ' now.'
			Select @OutputN
			Select Distinct tablename, index_name, indexdescription, index_keys, indid, stats, groupname, misc, WhenDescription from #ListB
			If @OSql = 'Y'
				BEGIN
					Set @OutputN = ''
					Set @OutputN = 'The folowing sql commands would re-create the indexes that previously existed on ' + @table + '.'
					Select @OutputN
					Declare @createindexsql varchar(512)
					Declare create_object_cursor insensitive cursor
					For Select Distinct tablename, indexdescription, index_name, index_keys, groupname, misc
					From #ListB
					where tablename = @table
					Open create_object_cursor
					fetch Next from create_object_cursor into @objname, @description, @index_name, @keys, @groupname, @misc
					While @@fetch_status = 0
					Begin

						Select @createindexsql = 'Create ' + 
						@description + 
						' index ' + 
						@index_name + 
						' on dbo.' + 
						@objname + 
						'(' + @keys + ')' +isnull( @misc,'') + ' on [' + @groupname + ']'
					--Select @createindexsql
						Begin
							Select @createindexsql
							--exec (@createindexsql)
						End

					fetch Next from create_object_cursor into @objname, @description, @index_name, @keys, @groupname, @misc

					End

				Close create_object_cursor
				deallocate create_object_cursor
				END
		END

IF @Before = 0 And @Now1 = 0
		BEGIN
			Set @OutputN = ''
			Set @OutputN = 'Indexes on ' + @table + ' now and on the date compared are identical. There are no differences.'
			Select @OutputN 
		END

Drop Table #ExistingIndexes
Drop Table #ListA
Drop Table #ListB
Set NOCOUNT Off




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

Share

Share

Rate