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

Labels

Comments

Unknown said…
Great proc! I've seen several proposed solutions to this issue, but this is by far the best I've found so far. I did run into one issue however. When generating clustered indexes to move heaps, it does so with a long list of columns instead of just a single column. The first table I attempted this on, the process failed because the combined length of data in the new clustered index fields exceeded 900 bytes. I tweaked the base procedure a bit to concatinate the first_ix_col_name instead of the index_columns field ( in two places ) and this corrected the problem. Just a patch, I'm sure, but it got me past my first attempt to use this.
Unknown said…
This is a great routine, thanks for sharing!
vinayaka cn said…
Thank you for your help
Anonymous said…
Mark,
This is a fantastic routine, thanks for sharing. I only needed to tweak it a bit for Unicode field types, and it worked great.

Popular posts from this blog

Being a fan of PowerBI, I recently looked for a way to read zip files directly into the Data Model, and found this blog which showed a usable technique. Inspired by the possibilities revealed in Ken's solution, but frustrated by slow performance, I set out to learn the M language and write a faster alternative. UnzipContents The result of these efforts is an M function - UnzipContents - that you can paste into any PowerBI / PowerQuery report. It takes the contents of a ZIP file, and returns a list of files contained therein, along with their decompressed data: If you're not sure how to make this function available in your document, simply: Open up PowerQuery (either in Excel or in PowerBI) Create a new Blank Query. Open up the Advanced Editor  (found on the View tab in PowerBI). Copy-Paste the above code into the editor, then close the editor. In the properties window, rename the the function to  UnzipContents Usage Using the function is fairly straight for...
Building Business Intelligence using the Microsoft BI stack in an organization that has an Oracle based transactional system is not uncommon, and here I'll outline a couple of tips and tricks that should ease the building of SSIS packages in that type of environment.   Attunity Components Attunity offer several nice solutions for getitng data out of Oracle and into SQL - one of which is their CDC (Change Data Capture) offering . If you don't have the budget or stomach for setting up CDC in an oracle environment, then your next best bet is to use the free Attunity oracle connectors for SSIS , which demonstrate a measurable performance boost.   The Oracle Index and Order By Gotcha In previous posts I mention the performance benefits of loading sorted data into target tables. I'm currently loading data from Oracle 10, which exhibits a rather strange characteristic... Oracle does not use make us of an index for ordering data .  In other words, Oracle only uses ...
Post a Comment