Use a Stored Procedure to Move User Tables Between File Groups

Downloads
99204.zip

Most DBAs use GUIs such as SQL Server 2005’s SQL Server Management Studio (SSMS) or SQL Server 2000’s Enterprise Manager to move tables between file groups. However, performing these tasks through the GUI is tedious, especially when you need to move a large number of them. In addition, using the GUI to move tables is prone to human errors, such as not moving all the tables that should be moved.

I developed a stored procedure, sp_MoveClst- TablesToFileGroup, that first finds the tables that need to be moved, then moves those tables and their data. Specifically, sp_MoveClstTablesToFileGroup looks for the clustered-indexed user tables you specify and moves those user tables from the old file group to the specified new file group. This stored procedure is especially helpful when you need to move user tables in the PRIMARY file group to another more-appropriate file group.

For the stored procedure to work, the new file group must already exist and contain one or more files. Because sp_MoveClstTablesToFileGroup uses the sp_ MsForEachTable system stored procedure to populate a global temporary table with the sp_HelpIndex results on all user tables, the executing user must be at least a database owner (DBO) who is able to create global temporary tables and execute system stored procedures from the master database.

Using sp_MoveClstTablesToFileGroup is easy. Suppose you want to change all the clustered-indexed user tables in the Pubs database from the PRIMARY file group to the NEWFG file group. Here are the steps you’d follow:

1. Compile sp_MoveClstTablesToFileGroup inside the Pubs database.
2. Log on to the server using a sysAdmin server role login or pubs dbOwner role.
3. Execute the stored procedure using the code
    USE pubs
     GO
     EXEC sp_MoveClstTablesToFileGroup
     @newFileGroup='NEWFG'
4. After running the stored procedure, check to see whether all of the clustered-indexed user tables residing in the Pubs database moved to the NEWFG file group from the PRIMARY file group.

I wrote sp_MoveClstTablesToFileGroup on SQL Server 2005 Developer Edition SP1, but it also works on SQL Server 2000. You can download the code file (ChangeObjectFG.sql) by clicking the 99204.zip hotlink above.

—Eli Leiba, senior application DBA, Israel Electric Company