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:
- Compile sp_MoveClstTablesToFileGroup inside the
Pubs database.
- Log on to the server using a sysAdmin server role
login or pubs dbOwner role.
- Execute the stored procedure using the code
USE pubs
GO
EXEC sp_MoveClstTablesToFileGroup
@newFileGroup='NEWFG'
- 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
End of Article
