June 13, 2005 at 4:28 am
I'm looking for some help on how to make this better, or even a better way to do it.
I'm looking to move all indexes (excluding clustered and text {indid 255} ) (there are a huge number of tables)
the script below works fine for those indexes, however it fails where the index is referenced by a primary key and also for system statistics (i guess those starting WA_)
how do I identify those indexes that are referenced by a primary key? - that way i can drop and recreate the key on the secondary filegroup
how do i identify if it is a statistic (possibly user created with a non WA_ name)?
alternatively has anyone got a better script.
declare @id integer
declare @tbname nvarchar(100)
declare @indid integer
declare @indname nvarchar(100)
declare @fill integer
declare @group integer
declare @list nvarchar(4000)
declare @strsql nvarchar(4000)
declare curs1 cursor for
SELECT TOP 100 PERCENT dbo.sysobjects.id,dbo.sysobjects.name,dbo.sysindexes.indid, dbo.sysindexes.name AS indname, dbo.sysindexes.OrigFillFactor,
dbo.sysindexes.groupid
FROM dbo.sysindexes INNER JOIN
dbo.sysobjects ON dbo.sysindexes.id = dbo.sysobjects.id
WHERE (dbo.sysobjects.xtype = 'U') AND (dbo.sysindexes.indid BETWEEN 2 AND 254) ORDER BY dbo.sysobjects.name, dbo.sysindexes.indid
open curs1
fetch next from curs1 into @id,@tbname,@indid,@indname,@fill,@group
while @@fetch_status=0
begin
set @list=''
SELECT @List = @List + '['+ dbo.syscolumns.name+'],'
FROM dbo.sysindexes INNER JOIN
dbo.sysobjects ON dbo.sysindexes.id = dbo.sysobjects.id INNER JOIN
dbo.sysindexkeys ON dbo.sysindexes.id = dbo.sysindexkeys.id AND dbo.sysindexes.indid = dbo.sysindexkeys.indid INNER JOIN
dbo.syscolumns ON dbo.sysindexkeys.id = dbo.syscolumns.id AND dbo.sysindexkeys.colid = dbo.syscolumns.colid
WHERE (dbo.sysobjects.xtype = 'U') AND (dbo.sysindexes.indid =@indid) AND (dbo.sysobjects.id = @id)
ORDER BY dbo.sysobjects.name, dbo.sysindexes.indid, dbo.sysindexkeys.keyno
set @list=left(@list,len(@list)-1)
select @tbname as tablename,@indname as indexname,@list as columnlist,@fill as fill
set @strsql='drop index '+@tbname+'.'+@indname
print @strsql
exec sp_executesql @strsql
set @strsql='CREATE INDEX ['+@indname+'] ON [dbo].['+@tbname+']('+@list+') WITH FILLFACTOR = 90 ON [SECONDARY]'--+convert(nvarchar(5),@fill)+' ON [SECONDARY]'
print @strsql
exec sp_executesql @strsql
fetch next from curs1 into @id,@tbname,@indid,@indname,@fill,@group
end
close curs1
deallocate curs1
many thanks
MVDBA
June 16, 2005 at 8:00 am
This was removed by the editor as SPAM
June 16, 2005 at 11:03 am
Today is a rushed day at work so addressing what seem to be the core questions...
1) "how do I identify those indexes that are referenced by a primary key?"
You could use sp_pkeys and join on sysobjects by name to identify the primary keys!
2) how do i identify if it is a statistic (possibly user created with a non WA_ name)?
Maybe you could do a not like '_WA_Sys%'.....
**ASCII stupid question, get a stupid ANSI !!!**
June 16, 2005 at 12:40 pm
Add the following to the "where" to exclude rows that are statistics (sysindexes.status & 64) = 0
Regarding your logic for indexes that are a physical implimentation of primary key or uniqueness constraints, you do not need to drop and recreate these constraints in order to change the index physical properties. The existing index can be replaced by using the "DROP_EXISTING" option.
From BOL:
Specifies that the named, preexisting clustered or nonclustered index should be dropped and rebuilt. The index name specified must be the same as a currently existing index. Because nonclustered indexes contain the clustering keys, the nonclustered indexes must be rebuilt when a clustered index is dropped. If a clustered index is recreated, the nonclustered indexes must be rebuilt to take the new set of keys into account.
The DROP_EXISTING clause enhances performance when re-creating a clustered index (with either the same or a different set of keys) on a table that also has nonclustered indexes. The DROP_EXISTING clause replaces the execution of a DROP INDEX statement on the old clustered index followed by the execution of a CREATE INDEX statement for the new clustered index. The nonclustered indexes are rebuilt once, and only if the keys are different.
If the keys do not change (the same index name and columns as the original index are provided), the DROP_EXISTING clause does not sort the data again. This can be useful if the index must be compacted.
A clustered index cannot be converted to a nonclustered index using the DROP_EXISTING clause; however, a unique clustered index can be changed to a non-unique index, and vice versa.
SQL = Scarcely Qualifies as a Language
June 17, 2005 at 1:39 am
Carl, thats great, Top Man - i've been looking for sysindexes.status breakdown everywhere.
with respect to the primary keys though how can i identify these? i can't afford to have this script run against them - i'm quite happy to leave the primary keys (and any related indexes) on the primary filegroup, but in the database there are about 100 tables with maybe a total of 300 indexes, so i need to be able to script out the move.
many thanks
MVDBA
June 17, 2005 at 2:04 am
SCRATCH THAT LAST QUESTION - I'VE GOT IT NOW
i can use objectproperty(tablename,'CnstIsNonclustkey') and objectproperty(tablename,'CnstIsclustkey')
MVDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply