November 18, 2004 at 4:01 pm
I am looking for a script which compares scripted index with database index based on name, index column sequence and sort order, fill factor, unique/clustered or non clustered. If everything matches for the same name index then do nothing else drop and create the index.
I need this script as many people touch on development/test enviornment and index consistency goes away. I don't want to delete all index and create all as the tables are very large and takes lot of time. I want which ever index is off, drop and recreate only those.
Please help me if somebody use this type of scripts for database maintenence.
November 22, 2004 at 8:00 am
This was removed by the editor as SPAM
November 22, 2004 at 9:16 am
I'm not sure what you are looking to compare? If you want the current fillfactor of the table, then it will be gathered by dbcc showcontig, but this only works for clustered indexes. There is no way I am aware of to get the fillfactor of a nonclustered index.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 22, 2004 at 10:32 am
I am looking for checking index when exists with sane defination. some thing in this line:
alter procedure usp_check_index_object
@Objname varchar(1000), @indexName varchar(8000), @decKeys varchar(4000), @decFillFactor int
as
declare @indid smallint,
-- @objname nvarchar(1000), --776
@i int,
@thiskey sysname,
@keys nvarchar(4000), --2078
@objid int,
-- @indexName varchar(8000),
@OrgFillFact int
select @objid = object_id(@objname)
--set @indid = 8
Select @indid = indid from sysindexes where indid > 0 And indid < 255 and [name] = @indexName
--print @indid
select @keys = index_col(@objname, @indid, 1), @i = 2
if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
select @keys = @keys + ' desc '
select @thiskey = index_col(@objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + ' desc '
While (@thiskey Is Not Null )
Begin
Select @keys = @keys + ', ' + @thiskey, @i = @i + 1
Select @thiskey = index_col(@objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + ' desc '
End
SELECT @OrgFillFact = OrigFillFactor FROM sysindexes WHERE name = @indexName
print @keys
print @OrgFillFact
if @decKeys <> @keys or @decFillFactor <> @OrgFillFact
print 'create index ' + @indexName + ' ON ' + 'dbo.' + @objname + '(' + @keys + ')' + 'with fillfactor = ' + cast(@OrgFillFact as varchar)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply