Easily Move SQL Tables between Filegroups

Recently during a Data Warehouse project, I had the need to move many tables to a new file group. I didn't like any of the solutions that I found on Google, so decided to create on of my own. The result?

MoveTablesToFilegroup

Click here for a nifty stored proc allows you to easily move tables, indexes, heaps and even LOB data to different filegroups without breaking a sweat. To get going, copy-paste the code below into Management Studio, and then run it to create the needed stored procedure.

Hopefully the arguments are self explanatory, but here are some examples:

1. Move all tables, indexes and heaps, from all schemas into the filegroup named SECONDARY:

EXEC dbo.sp_MoveTablesToFileGroup
@SchemaFilter = '%', -- chooses schemas using the LIKE operator
@TableFilter  = '%', -- chooses tables using the LIKE operator
@DataFileGroup = 'SECONDARY', -- The name of the filegroup to move index and in-row data to.
@ClusteredIndexes = 1, -- 1 means "Move all clustered indexes" - i.e. table data where a primary key / clustered index exists
@SecondaryIndexes = 1, -- 1 means "Move all secondary indexes"
@Heaps = 1, -- 1 means "Move all heaps" - i.e. tables with no clustered index.
@ProduceScript = 1 -- Don't move anything, just produce a T-SQL script

2. Produce a script to move LOBS to the LOB_DATA filegroup, and move table data to the SECONDARY filegroup, for tables in the TEST schema only:

EXEC dbo.sp_MoveTablesToFileGroup
@SchemaFilter = 'TEST', -- Only tables in the TEST schema
@TableFilter  = '%', -- All tables
@DataFileGroup = 'SECONDARY', -- Move in-row data to SECONDARY
@LobFileGroup =  'LOB_DATA', -- Move LOB data to LOB_DATA fg.
@ClusteredIndexes = 1, -- Move all clustered indexes
@SecondaryIndexes = 0, -- Don't move all secondary indexes
@Heaps = 0, -- Don't move tables with no PK
@ProduceScript = 1 -- Don't move anything, just produce a T-SQL script