The Best Script for --Move a table between filegroups

http://www.tsql.ca/Resources/Articles/MoveTablesbetweenFilegroups/tabid/610/Default.aspx

Sometimes we need to move tables between filegroups. There are two options how to do it.

The first option is to rename existing table, create a new one with the same structure on the desired filegroup(s), copy the data over and then to drop the original table. It requires log space, some outage for the system, risk of having problems with security, and so on. But this is the only option if table has BLOB fields.

The second option is to recreate table’s clustered index on the desired filegroup. It will move the table itself on that filegroup. As it was mentioned the BLOBs field will have problems – they are stored separately from the table’s data pages and are not affected by any index operation.I had to reallocate all the tables in multiple databases, to put them into new filegroups.

Since option #1 was not good for some reasons I implemented such operation as a script. Script moves tables to proper filegroup if it's not aligned with desired filegroup specified in the configuration list. Script may operate with multiple databases at once, so one may specify tables from different databases.

Script structure:

A configuration list (database, table, filegroup) is created.
Existence of all filegroups is checked - quits if there is an assumed filegroup that does not exist in the database.
Every table in the database is checked against that list.
For those having different filegroup a script is built and preserved: that script re-creates existing indexes (creates with drop existing) I.e. it repeats existing indexes for the table with the new filegroup specified. Since some of the non-clustered indexes may be located in a different filegroup, they are moved to the table's filegroup. See index (re-)creation script description below.
Those scripts are executed within a transaction one-by-one.


Index (re-)creation script structure:

compose drop index commands list create index commands list after existing indexes for non-clustered indexes which are located not in the desired filegroup or if the clustered index for the table is not in the desired filegroup.
compose create index (with drop existing) commands for the clustered indexes that are to be moved. For tables without any clustered index the PK supporting index is made clustered and recreated with other.
compose create index commands list after existing indexes for non-clustered indexes which are located not in the desired filegroup or if the clustered index for the table is not in the desired filegroup. The index creation command does not include filegroup name, so it will be created in the same filegroup as the table it belongs to.
Those commands are saved in a table with following structure:

record id (identity)
table name
sequence number (the same as topics listed just above)
index name
sql command
The very structure manipulation script will go through that table in order of table name then sequence number then index name (latter is just for convinience - to analyze the log). There will be a transaction for each table - too costly to make it around everything.

The script comments verbosely its execution process at the run time.

Script is also is assumed to be a tool for operational DBA team parameterized by the table with tables/filegroups (see statements inserting data into #desired_layout table variable).

In order to get all the messages in a printable form the script output must be directed to text or a file.

/*******************************************************************************

** Name: Database tables moved to proper filegroups.sql

** Description: Script moves tables to proper filegroup if it's not aligned

with a list which is derived from the model(s).



Script structure:

- A list (database, table, filegroup) is created.

- Existence of all filegroups is checked - quits if there is an

assumed filegroup that does not exist in the database.

- Every table in the database is checked against that list.

- For those having different filegroup a script is built

and preserved: that script re-creates existing indexes

(creates with drop existing)

I.e. it repeats existing indexes

for the table with the new filegroup specified.

Since some of the non-clustered indexes may be located in

a different filegroup, they are moved to the table's filegroup.

See index (re-)creation script description below.

- Those scripts are executed within a transaction one-by-one.



Index (re-)creation script structure:

1) compose drop index commands list create index commands list

after existing indexes

for non-clustered indexes which are located not in the desired

filegroup or if the clustered index for the table is not in the

desired filegroup.

2) compose create index (with drop existing) commands for the clustered

indexes that are to be moved. For tables without any clustered

index the PK supporting index is made clustered and recreated with other.

3) compose create index commands list after existing indexes

for non-clustered indexes which are located not in the desired

filegroup or if the clustered index for the table is not in the

desired filegroup. The index creation command does not include

filegroup name, so it will be created in the same filegroup

as the table it belongs to.

Those commands are saved in a table with following structure:

- record id (identity)

- table name

- sequence number (the same as topics listed just above)

- index name

- sql command

The very structure manipulation script will go through that table

in order of table name then sequence number then index name

(latter is just for convinience - to analyze the log).

There will be a transaction for each table - too costly to make

it around everything.



Script comments verbosely its execution process at the run time.



Script is also is assumed to be a tool for operations team

parameterized by the table with tables/filegroups

(see statements inserting data into #desired_layout table variable).



In order to get all the messages in a printable form the script

output must be directed to text or a file.





**

** Input Parameters: database/table/filegroup mapping in a form

of insert statements (see configurable part below).

** Returns: a table with database/table/index names with filegroup names

for each object: original one, configured(desired) one and the final one,

status of the operation for each index is specified.



** Modification History

** Sr Date Modified By Reason

** 1 05/01/2007 Alexander Karmanov Initial version

*******************************************************************************/

use master

go

set nocount on

declare

@script_name sysname

select

@script_name = 'Database tables moved to proper filegroups.sql'



print replicate('-', 80)

print 'Script name: ' @script_name

print 'Server: ' @@servername

print 'Database: ' db_name()

print 'User: ' suser_sname()



print 'Start time: ' cast(getdate() as varchar)

print replicate('-', 80)



go



/******************************************************************************

Here is a configurable part:

mapping between tables and filegroups

*******************************************************************************/

-- declare a table with desired database tables layout

if object_id('tempdb..#desired_layout') is not null drop table #desired_layout

create table #desired_layout (database_name sysname, table_name sysname, filegroup_name sysname)

insert into #desired_layout (database_name, table_name, filegroup_name)

-- database_name_1

/* database_name_1 is not moved for a while */

-- database_name_2

select 'database_name_2', 'address_type', 'srv001_db_2_group_1' union all

<....>

select 'database_name_2', 'workflow_status', 'srv001_db_2_group_x'



/******************************************************************************

End of configurable part,

below goes the actual code

*******************************************************************************/

go

use tempdb

go

-- a table with the column list for all the indexes in the source database is prepared,

-- but is left emtpy - it's needed to be created prior to a helper UDF

if object_id('tempdb..index_columns') is not null drop table index_columns

create table index_columns (database_name sysname, table_name sysname, index_name sysname,

column_name sysname, key_no int,

is_index_clustered bit, is_index_unique bit, is_column_descending bit)

go

-- a helper function converting a table with column names into a comma-separated list

if object_id('tempdb..f_get_column_list') is not null drop function f_get_column_list

go

create function dbo.f_get_column_list (

@database_name sysname,

@table_name sysname,

@index_name sysname

)

returns varchar(8000)

as

begin

declare @col_list varchar(8000)

select @col_list = isnull(@col_list ', ', '')

column_name ' '

case is_column_descending when 1 then 'DESC' else '' end

from index_columns

where database_name = @database_name

and table_name = @table_name

and index_name = @index_name

order by key_no

return @col_list

end

go



-- after that point the only 'go' command is the very final one - many variables are reused.

declare

@sql varchar(8000),

@crlf char(2),

@rowcnt int

select

@crlf = char(13) char(10),

@sql = ''



-- check for existence of all the filegroups mentioned in the layout table, exit if there is any problem:

-- get a list of existing filegroups for all the databases:

if object_id('tempdb..#existing_filegroups') is not null drop table #existing_filegroups

create table #existing_filegroups (database_name sysname, filegroup_name sysname)

select @sql = @sql @crlf 'select ''' database_name ''' as database_name, groupname as group_name from '

database_name '.dbo.sysfilegroups union all'

from #desired_layout

group by database_name

select

@sql = replace (@sql ';', 'union all;', '')

insert into #existing_filegroups (database_name, filegroup_name)

exec(@sql)



(select @rowcnt = count(distinct d.filegroup_name)

from #desired_layout d

left join #existing_filegroups e

on d.database_name = e.database_name

and d.filegroup_name = e.filegroup_name

where e.database_name is null)

if @rowcnt > 0

begin

select 'In ' upper(d.database_name) ' database there is no filegroup ''' upper(d.filegroup_name) ''''

as 'Filegroups that does not exist in the target database:'

from #desired_layout d

left join #existing_filegroups e

on d.database_name = e.database_name

and d.filegroup_name = e.filegroup_name

where e.database_name is null

group by d.database_name, d.filegroup_name

print 'Script is aborted because there is at least one filegroup that does not exist (see above).'

return

end

print 'Check for the missing filegroups completed successfully - all the filegroups mentioned in the list do exist.'



-- get the existing database/table/index/filegroup layout

if object_id('tempdb..#existing_layout') is not null drop table #existing_layout

create table #existing_layout (database_name sysname, table_name sysname, index_name sysname, ind_id int,

filegroup_name sysname, is_pk_or_uk int, table_filegroup_name sysname)

select @sql = null

select @sql = isnull(@sql @crlf 'union all' @crlf, '') 'select ''' database_name ''' as database_name, ' @crlf

'o.name as table_name, i.name as index_name, i.indid as ind_id, g.groupname as filegroup_name, ' @crlf

'case when k.name = i.name then 1 else 0 end as is_pk_or_uk, ' @crlf

'g2.groupname as table_filegroup_name ' @crlf

' from ' database_name '.dbo.sysobjects o ' @crlf

' join ' database_name '.dbo.sysindexes i ' @crlf

' on o.id = i.id ' @crlf

' join ' database_name '.dbo.sysfilegroups g ' @crlf

' on i.groupid = g.groupid ' @crlf

' left join (select c.id, o2.name ' @crlf

' from ' database_name '.dbo.sysconstraints c ' @crlf

' join ' database_name '.dbo.sysobjects o2 ' @crlf

' on c.constid = o2.id ' @crlf

' where o2.xtype in (''PK'', ''UQ'')) k ' @crlf

' on o.id = k.id ' @crlf

' join ' database_name '.dbo.sysindexes i2 ' @crlf

' on o.id = i2.id ' @crlf

' and i2.indid < 2 ' @crlf

' join ' database_name '.dbo.sysfilegroups g2 ' @crlf

' on i2.groupid = g2.groupid ' @crlf

' where o.xtype = ''U'' ' @crlf

' and i.name not like ''\_WA%'' escape ''\'' ' @crlf

' --and indexproperty(object_id(o.name), i.name, ''isstatistics'') = 0 ' @crlf

' and i.indid < 255 ' @crlf

from #desired_layout

group by database_name

insert into #existing_layout (database_name, table_name, index_name, ind_id, filegroup_name, is_pk_or_uk, table_filegroup_name)

exec(@sql)



-- compose table with commands changing the database structure

if object_id('tempdb..#sql') is not null drop table #sql

create table #sql (rowid int identity (1,1), database_name sysname, table_name sysname,

index_name sysname, seq_number int, sql varchar(7000))



-- get all the indexes that must be dropped:

-- 1. indexes that are not clustered, are not created as PK or UNIQUE constraint.

-- 2. either:

-- 2a. index is located not in a desired filegroup

-- 2b. clustered index on the table (index belongs to) is located not in a desired filegroup

-- All those indexes got sequence code of 1 meaning it will be executed first.

insert #sql (database_name, table_name, index_name, seq_number, sql)

select distinct e.database_name, e.table_name, e.index_name, 1 as seq_number,

sql = 'use ' e.database_name '; drop index dbo.' e.table_name '.' e.index_name

from #existing_layout e

join #desired_layout d

on e.database_name = d.database_name

and e.table_name = d.table_name

where e.is_pk_or_uk = 0

and e.ind_id > 1

and (

e.filegroup_name <> d.filegroup_name

or

e.table_filegroup_name <> d.filegroup_name

)





-- get all the indexes that must be created as clustered with drop_existing option:

-- 1. all the indexes that are clustered or PK or unique

-- (all the PK indexes on the tables that don't have clustered key get here as PK/UQ so the distinction needs to be done -

-- when the CLUSTERED word is included.)

-- All those indexes got sequence code of 2 meaning they will be executed when all the dependant indexes are dropped

-- The very command is to be added later - now just identifiers.

insert #sql (database_name, table_name, index_name, seq_number, sql)

select distinct e.database_name, e.table_name, e.index_name, 2 as seq_number,

sql = 'use ' e.database_name '; create %unique% %clustered% index ' e.index_name ' on dbo.' e.table_name

'(%column_list%) with fillfactor = 90, drop_existing on ' quotename(d.filegroup_name)

from #existing_layout e

join #desired_layout d

on e.database_name = d.database_name

and e.table_name = d.table_name

where

e.is_pk_or_uk = 1 -- check for PK or unique key

or e.ind_id = 1 -- indid in sysindexes means clustered key on table





-- get all the indexes that are dropped in the first step - with the new destination filegroup and

-- all the parameters like fillfactor, and so on.

-- no need to analyze layouts, just take those that were marked as to be dropped and

-- compose create index statement for them.

insert #sql (database_name, table_name, index_name, seq_number, sql)

select distinct s.database_name, s.table_name, s.index_name, 3 as seq_number,

sql = 'use ' s.database_name '; create %unique% nonclustered index ' s.index_name ' on dbo.' s.table_name

'(%column_list%) with fillfactor = 90 on ' quotename(d.filegroup_name)

from #sql s

join #desired_layout d

on s.database_name = d.database_name

and s.table_name = d.table_name

where seq_number = 1



-- get attributes, column lists from the existing database

select @sql = null

select @sql = isnull(@sql @crlf, '')

'use ' database_name @crlf

'insert into tempdb..index_columns (database_name, table_name, index_name, column_name, ' @crlf

' key_no, is_index_clustered, is_index_unique, is_column_descending)' @crlf

'select ''' database_name ''', o.name as table_name, i.name as index_name, c.name as column_name, k.keyno, ' @crlf

' INDEXPROPERTY (o.id, i.name, ''isclustered'') as is_index_clustered,' @crlf

' INDEXPROPERTY (o.id, i.name, ''isunique'') as is_index_unique,' @crlf

' INDEXKEY_PROPERTY(o.id, i.indid, k.keyno, ''isdescending'') as is_column_descending' @crlf

' from ' database_name '.dbo.sysobjects o' @crlf

' join ' database_name '.dbo.sysindexes i' @crlf

' on o.id = i.id' @crlf

' join ' database_name '.dbo.sysindexkeys k' @crlf

' on i.id = k.id' @crlf

' and i.indid = k.indid' @crlf

' join ' database_name '.dbo.syscolumns c' @crlf

' on k.colid = c.colid' @crlf

' and k.id = c.id' @crlf

' where ' @crlf

' o.xtype = ''U''' @crlf

' and INDEXPROPERTY (i.id, i.name, ''isstatistics'') = 0' @crlf

''

from #desired_layout

group by database_name

exec(@sql)



-- replace %column_list% placeholders in the index creation statements with proper column list

update #sql

set sql = replace(sql, '%column_list%', dbo.f_get_column_list(database_name, table_name, index_name))



-- specify proper clustered option for indexes:

-- for indexes that are clustered already:

update s

set sql = replace(sql, '%clustered%', 'clustered')

from #sql s

join tempdb..index_columns ic

on s.database_name = ic.database_name

and s.table_name = ic.table_name

and s.index_name = ic.index_name

and ic.is_index_clustered = 1

-- for indexes that are PK in table without any clustered index - we make PK indexes clustered,

-- so it will be rebuilt in the new (desired) filegroup.

update s

set sql = replace(sql, '%clustered%', 'clustered')

from #sql s

join tempdb..index_columns ic

on s.database_name = ic.database_name

and s.table_name = ic.table_name

and s.index_name = ic.index_name

join #existing_layout e

on s.database_name = e.database_name

and s.table_name = e.database_name

and e.ind_id = 0 -- that means the table is a heap right now (no clustered index)

-- for the rest set it as nonclustered

update s

set sql = replace(sql, '%clustered%', 'nonclustered')

from #sql s



-- specify proper unique option for indexes:

-- for indexes that are clustered already:

update s

set sql = replace(sql, '%unique%', 'unique')

from #sql s

join tempdb..index_columns ic

on s.database_name = ic.database_name

and s.table_name = ic.table_name

and s.index_name = ic.index_name

and ic.is_index_unique = 1

-- for the rest set it as non-unique

update s

set sql = replace(sql, '%unique%', '')

from #sql s



-- at this point all the commands are prepared in #sql table

-- and all we need is just to execute them one-by-one.

-- Actions for a table are wrapped into a transaction,

-- so if there is any error no table indexes structures

print cast(getdate() as varchar) ': Starting processing the tables'

declare

@rowid int,

@database_name sysname,

@table_name sysname,

@index_name sysname,

@seq_number int

declare t cursor static for

select distinct database_name, table_name

from #sql

order by database_name, table_name

open t

fetch next from t into @database_name, @table_name

while @@fetch_status = 0

begin

print cast(getdate() as varchar) ': Processing table ' @table_name ' in ' @database_name ' database'

begin tran

declare i cursor static for

select rowid, index_name, sql, seq_number

from #sql

where database_name = @database_name

and table_name = @table_name

order by seq_number, index_name

open i

fetch next from i into @rowid, @index_name, @sql, @seq_number

while @@fetch_status = 0

begin

if @seq_number = 1 print cast(getdate() as varchar) ': Dropping index ' @table_name '.' @index_name

else if @seq_number = 2 print cast(getdate() as varchar) ': Moving index ' @table_name '.' @index_name ' to another filegroup'

else if @seq_number = 3 print cast(getdate() as varchar) ': Re-creating index ' @table_name '.' @index_name



print cast(getdate() as varchar) ': A command to be executed: ' @sql

exec(@sql)

-- if there is an error - stop processing that table, rolling back transaction for it and reporting failure for it

if @@error <> 0

begin

rollback

print cast(getdate() as varchar) ': Command failed to execute'

break

end

print cast(getdate() as varchar) ': Command successfully completed'

fetch next from i into @rowid, @index_name, @sql, @seq_number

end

close i

deallocate i



-- if we have uncommitted transaction here that means no error occured in th loop on the indexes

-- and there is no transaction rollback issued above - i.e. everything is fine for that table,

-- report success on it (and commit it of course):

if @@trancount > 0

begin

commit

print cast(getdate() as varchar) ': Successfully processed table ' @table_name ' in ' @database_name ' database'

end

fetch next from t into @database_name, @table_name

end -- of loop on t cursor

close t

deallocate t



-- get the tables layout after the changes (similar to what was done before the changes:

-- get the existing database/table/index/filegroup layout

if object_id('tempdb..#updated_layout') is not null drop table #updated_layout

create table #updated_layout (database_name sysname, table_name sysname, index_name sysname, ind_id int,

filegroup_name sysname, is_pk_or_uk int, table_filegroup_name sysname)

select @sql = null

select @sql = isnull(@sql @crlf 'union all' @crlf, '') 'select ''' database_name ''' as database_name, ' @crlf

'o.name as table_name, i.name as index_name, i.indid as ind_id, g.groupname as filegroup_name, ' @crlf

'case when k.name = i.name then 1 else 0 end as is_pk_or_uk, ' @crlf

'g2.groupname as table_filegroup_name ' @crlf

' from ' database_name '.dbo.sysobjects o ' @crlf

' join ' database_name '.dbo.sysindexes i ' @crlf

' on o.id = i.id ' @crlf

' join ' database_name '.dbo.sysfilegroups g ' @crlf

' on i.groupid = g.groupid ' @crlf

' left join (select c.id, o2.name ' @crlf

' from ' database_name '.dbo.sysconstraints c ' @crlf

' join ' database_name '.dbo.sysobjects o2 ' @crlf

' on c.constid = o2.id ' @crlf

' where o2.xtype in (''PK'', ''UQ'')) k ' @crlf

' on o.id = k.id ' @crlf

' join ' database_name '.dbo.sysindexes i2 ' @crlf

' on o.id = i2.id ' @crlf

' and i2.indid < 2 ' @crlf

' join ' database_name '.dbo.sysfilegroups g2 ' @crlf

' on i2.groupid = g2.groupid ' @crlf

' where o.xtype = ''U'' ' @crlf

' and i.name not like ''\_WA%'' escape ''\'' ' @crlf

' --and indexproperty(object_id(o.name), i.name, ''isstatistics'') = 0 ' @crlf

' and i.indid < 255 ' @crlf

from #desired_layout

group by database_name

insert into #updated_layout (database_name, table_name, index_name, ind_id, filegroup_name, is_pk_or_uk, table_filegroup_name)

exec(@sql)



-- report difference between the previously existing layout, what was the intent and what we've got after the update:

print 'Script execution status report (see the grid if it is not displayed below):'

select

e.database_name '.' e.table_name '.' e.index_name as object,

e.filegroup_name as original_fileroup,

isnull(d.filegroup_name, 'NOT CONFIGURED') as desired_new_filegroup,

isnull(u.filegroup_name, 'OBJECT DISAPPEARED!!!') as actual_new_filegroup,

case

when d.filegroup_name is null then 'NOT CONFIGURED'

when u.filegroup_name is null then 'OBJECT WAS LOST'

when d.filegroup_name <> u.filegroup_name then 'NO UPDATE HAPPENED'

else 'ok'

end as update_status

from #existing_layout e

left join #updated_layout u

on e.database_name = u.database_name

and e.table_name = u.table_name

and e.index_name = u.index_name

left join #desired_layout d

on e.database_name = d.database_name

and e.table_name = d.table_name

where e.ind_id > 0 -- no need to show items for the heap (which item is not actually an index anyway).

-- If such heap table has a PK it is shown in a separate line (with ind_id > 1)

order by e.database_name, e.table_name, e.index_name

go

-- clean-up

use tempdb

if object_id('tempdb..f_get_column_list') is not null drop function f_get_column_list

if object_id('tempdb..index_columns') is not null drop table index_columns

go

-- to release any database we switched to

use master

go

-- the end of the script



/*

select *

from #desired_layout



select *

from #updated_layout

order by table_name



select *

from #existing_layout

order by table_name



select *

from #sql

order by table_name



*/