Move tables and indexes from certain schema to a different filegroup

Moving tables from a schema to a new filegroup might be tricky and it is even worse when the schema is not dbo. To easily address that I have setup a script that move the objects to schema dbo (using the script from Roberto), execute the move and then switch back to the original schema.

Pre requisite: Implement Roberto’s stored procedure (here)

Once you have the stored procedure setup, generate the necessary scripts to perform the move by using the script below (remember to change the schema name and filegroup ID, marked in red).

-- Prepare to move SAMPLE schema objects to 
-- NEW filegroup
-- Make sure that @TargetFileGroupID is set to 
-- the appropriate filegroup ID
select 'ALTER SCHEMA [dbo] TRANSFER [SAMPLE].['+t.name+']; 
GO'
from sys.schemas s
join sys.tables t
on t.schema_id = s.schema_id
where s.name = ('SAMPLE')
UNION ALL
select 'exec sp_move_tables @SourceFileGroupID = 1, 
 @TargetFileGroupID = 2, 
 @TableToMove = ['+t.name+'], 
 @MovePKAndAllUniqueConstraints = 1, 
 @MoveAllNonClusteredIndexes = 1 
GO' 
from sys.schemas s
join sys.tables t
on t.schema_id = s.schema_id
where s.name = ('SAMPLE')
UNION ALL
select 'ALTER SCHEMA [SAMPLE] TRANSFER [dbo].['+t.name+']; 
GO'
from sys.schemas s
join sys.tables t
on t.schema_id = s.schema_id
where s.name = ('SAMPLE')

Simply copy the output of the query and run it on a new query window.

If you are not sure what is the Filegroup ID, you can confirm by executing the command below with your Filegroup Name:

select FILEGROUP_ID('FG_NAME')
Advertisements

About Bruno Carvalho

Coffee addicted tech guy.
This entry was posted in Scripts, SQL Server and tagged , , , , , , , , . Bookmark the permalink.

2 Responses to Move tables and indexes from certain schema to a different filegroup

  1. Taylor Davis says:

    I actually took Roberto’s script and re-wrote it to use schema naming: https://gallery.technet.microsoft.com/scriptcenter/SQL-Server-Move-Tables-c5075ed2

    I had the joy of moving an entire schema to a new filegroup and wrapped my modified sproc in a cursor so I could move all tables in the schema in one go. Let me know what you think.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s