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), 
@miscnvarchar(210),
@index_name varchar(70),
@createindexsql varchar(512),
@errorstrvarchar(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), 
@miscnvarchar(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