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 
