Drop and recreate indexes in new filegroup
-
I'm wanting to drop all my current indexes in my database and re-create all of them again into a new filegroup. Does anybody know how to script this out easily? There are about 1500 indexes in this database, so it wouldn't be practical to do it by hand with Enterprise Manager.
-
Hi Perry,
Try this code, it only generates the rebuild statements, so you can review it before you actually execute the code:
declare c_idx cursor local fast_forward
for
select o.id, i.indid
, o.name table_name
, i.name index_name
, isnull(indexproperty(o.id, i.name, 'PadIndex'), 0)
, indexproperty(o.id, i.name, 'IndexFillFactor')
, case indexproperty(o.id, i.name, 'IsUnique')
when 1 then 'unique '
else ''
end
, c.name column_name
from sysindexes i
inner join sysobjects o
on i.id = o.id
inner join sysindexkeys ik
on ik.id = o.id
and ik.indid = i.indid
inner join syscolumns c
on o.id = c.id
and ik.colid = c.colid
-- only indexes for user tables
where o.xtype = 'U'
-- no automatically created indexes
and i.name not like '_WA_Sys_%'
-- no clustered indexes
and i.indid != 1
-- sort by table, index and index-column order
order by o.name, i.name, ik.keyno
-- variable for filegroup name
declare @NEW_FILEGROUP varchar(100)
set @NEW_FILEGROUP = 'NEW_FILEGROUP'
declare @id int
declare @indid int
declare @table_name sysname
declare @index_name sysname
declare @pad_index bit
declare @fillfactor int
declare @uniqueClause varchar(10)
declare @column_name sysname
declare @currId int
declare @currIndid int
declare @sql varchar(4000)
-- initialize @id and @indid for "not equal" comparison
set @currId = -1
set @currIndid = -1
open c_idx
fetch next from c_idx
into @id, @indid, @table_name, @index_name
, @pad_index, @fillfactor, @uniqueClause, @column_name
while @@fetch_status = 0
begin
-- if @id or @indid have changed, we'll create a new
-- "create index" statement
if (@id != @currId) or (@indid != @currIndid)
begin
-- first, we have to finish and print the previous statement,
-- if any (@currId != -1)
if @currId != -1
begin
-- close column list and start index_options clause
set @sql = @sql + ')' + char(10) + 'with '
-- add index oprions pad_index and fillfactor
if @pad_index = 1
set @sql = @sql + 'pad_index' + char(10)
set @sql = @sql + 'fillfactor=' + cast(@fillfactor as varchar(10)) +
char(10) + 'drop_existing' + char(10) +
'on ' + @NEW_FILEGROUP + char(10) + 'go' + char(10)
print(@sql)
end
-- start a new create index statement
set @sql = 'create ' + @uniqueClause + 'index ' + @index_name + char(10) +
'on ' + @table_name + ' ('
set @currId = @id
set @currIndid = @indid
end
-- add column to list, ommit comma for first column
if right(@sql, 1) = '('
set @sql = @sql + @column_name
else
set @sql = @sql + ', ' + @column_name
fetch next from c_idx
into @id, @indid, @table_name, @index_name
, @pad_index, @fillfactor, @uniqueClause, @column_name
end
-- don't forget to close the last statement too
if @currId != -1
begin
-- close column list and start index_options clause
set @sql = @sql + ')' + char(10) + 'with '
-- add index oprions pad_index and fillfactor
if @pad_index = 1
set @sql = @sql + 'pad_index' + char(10)
set @sql = @sql + 'fillfactor=' + cast(@fillfactor as varchar(10)) +
char(10) + 'drop_existing' + char(10) +
'on ' + @NEW_FILEGROUP + char(10) + 'go' + char(10)
print(@sql)
end
close c_idx
deallocate c_idx
Good luck and cheers,
Henk
-
Henk,
This is an excellent script. I'd like to add my two cents worth on things that might be added to it to make it more bullet-proof...
1 - You only considered tables with PK's. If you change the where clause in the cursor from 'and i.indid != 1' to 'and i.indid not in (0,1)', you'd also get the heaps
2 - Sometimes (why? I don't know), indexes are built with descending keys. If you include logic with indexkey_property on each column, you could identify those that are descending and could include it on the CREATE INDEX syntax
3 - Not all statistics are named '_WA_Sys%'. Instead, you could change the cursor's where clause from 'and i.name not like '_WA_Sys%' to 'and INDEXPROPERTY('IsStatistics')', you're sure to get them all.
4 - The last item is to help with restartability. I'd add logic in the cursor to ignore any index not in the new FILEGROUP already.
- again, just my two cents worth
-
Hey bbdpres en Perry,
I've integrated all but one of bbdpres's very usefull suggestions in the script below. I didn't do anything with indid = 0 (heap table), because I found that when I created a table with only a nonclustered indexes (even the PK), an extra index shows up in sysindexes with indid = 0, but this index doesn;t have any sysindexkeys records, so is automatically not selected in the cursor because it has an inner join to that table. I did change the indid != 1 to indexproperty(id, name, 'IsClustered') because it makes the code better readable though.
Thanks for your valuable input bbdpres!
-- variable for filegroup name
declare @NEW_FILEGROUP sysname
set @NEW_FILEGROUP = N'NEW_FILEGROUP'
declare @fileGroupId smallint
-- initialize @fileGroupId (needed for testing,
-- because I don't have a NEW_FILEGROUP filegroup)
set @fileGroupId = -1
select @fileGroupId = groupid
from sysfilegroups
where groupname = @NEW_FILEGROUP
declare c_idx cursor local fast_forward
for
select o.id, i.indid
, o.name table_name
, i.name index_name
, isnull(indexproperty(o.id, i.name, 'PadIndex'), 0)
, indexproperty(o.id, i.name, 'IndexFillFactor')
, case indexproperty(o.id, i.name, 'IsUnique')
when 1 then 'unique '
else ''
end
, case indexkey_property(o.id, i.indid, ik.keyno, 'IsDescending')
when 1 then ' desc'
else ''
end
, c.name column_name
from sysindexes i
inner join sysobjects o
on i.id = o.id
inner join sysindexkeys ik
on ik.id = o.id
and ik.indid = i.indid
inner join syscolumns c
on o.id = c.id
and ik.colid = c.colid
-- only indexes for user tables
where o.xtype = 'U'
-- no automatically created indexes
and isnull(indexproperty(o.id, i.name, 'IsStatistics'), 1) = 0
-- no clustered indexes
and indexproperty(o.id, i.name, 'IsClustered') = 0
-- do not select index that are already in the destination filegroup
and groupid != @fileGroupId
-- sort by table, index and index-column order
order by o.name, i.name, ik.keyno
declare @id int
declare @indid int
declare @table_name sysname
declare @index_name sysname
declare @pad_index bit
declare @fillfactor int
declare @uniqueClause varchar(10)
declare @descClause varchar(10)
declare @column_name sysname
declare @currId int
declare @currIndid int
declare @sql varchar(4000)
-- initialize @id and @indid for "not equal" comparison
set @currId = -1
set @currIndid = -1
open c_idx
fetch next from c_idx
into @id, @indid, @table_name, @index_name
, @pad_index, @fillfactor, @uniqueClause
, @descClause, @column_name
while @@fetch_status = 0
begin
-- if @id or @indid have changed, we'll create a new
-- "create index" statement
if (@id != @currId) or (@indid != @currIndid)
begin
-- first, we have to finish and print the previous statement,
-- if any (@currId != -1)
if @currId != -1
begin
-- close column list and start index_options clause
set @sql = @sql + ')' + char(10) + 'with '
-- add index oprions pad_index and fillfactor
if @pad_index = 1
set @sql = @sql + 'pad_index' + char(10)
set @sql = @sql + 'fillfactor=' + cast(@fillfactor as varchar(10)) +
char(10) + 'drop_existing' + char(10) +
'on ' + @NEW_FILEGROUP + char(10) + 'go' + char(10)
print(@sql)
end
-- start a new create index statement
set @sql = 'create ' + @uniqueClause + 'index ' + @index_name + char(10) +
'on ' + @table_name + ' ('
set @currId = @id
set @currIndid = @indid
end
-- add column to list, ommit comma for first column
if right(@sql, 1) = '('
set @sql = @sql + @column_name + @descClause
else
set @sql = @sql + ', ' + @column_name + @descClause
fetch next from c_idx
into @id, @indid, @table_name, @index_name
, @pad_index, @fillfactor, @uniqueClause
, @descClause, @column_name
end
-- don't forget to close the last statement too
if @currId != -1
begin
-- close column list and start index_options clause
set @sql = @sql + ')' + char(10) + 'with '
-- add index oprions pad_index and fillfactor
if @pad_index = 1
set @sql = @sql + 'pad_index' + char(10)
set @sql = @sql + 'fillfactor=' + cast(@fillfactor as varchar(10)) +
char(10) + 'drop_existing' + char(10) +
'on ' + @NEW_FILEGROUP + char(10) + 'go' + char(10)
print(@sql)
end
close c_idx
deallocate c_idx
Cheers,
Henk
-
Thank you very much for all your suggestions. It is very helpful.
-
Excellent! just what I was After
-
You can find a reliable and more robust script to move the table data to a new filegroup on
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply