I needed to move all the tables and related indexes to another file group but didn't want to drop and re-create the database... I had a Google, and found some SQL on http://www.sqlmag.com/Article/ArticleID/97018/sql_server_97018.html which moved a given table to another given file group. I updated it to move all the tables not already on that group.
Another thing I noticed when playing around with this was the behaviour in SQL Server Management Studio was that it always specifies the file group when you script off an index. I just wanted it to use the default file group specified. This was annoying because the script we sent to a customer now has our default file group named in it, which they don't have setup. If you don't specify a file group then it will use the one configured as the default. So, I also had to modify all the scripts to not specify a file group, somewhat of an annoyance... Therefore, if you don't want to have the hassle of modifying the generated script, the script to move the tables works well! If anyone knows how to make SQL not specify a file group please let me know!
The SQL below is a modified version of the one found in the link above - go there for more information. All I changed was fix a bug where you needed the [ ] around index / table names when calling OBJECT_ID (it doesn't like the . in the names of the objects as . separates the schema name). Also added the loop for each table, the original script you specified a table name.
SET NOCOUNT ON
-- Note - @SourceFileGroupID and @TargetFileGroupID are the IDs of the
-- source and destination file groups. If you are not sure what
-- the IDs of your file groups are, simply run sp_helpfilegroup
-- and look at the groupid column of the result set.
DECLARE @SourceFileGroupID INT
DECLARE @TargetFileGroupID INT
DECLARE @TableToMove NVARCHAR(128)
DECLARE @MovePKAndAllUniqueConstraints BIT
DECLARE @MoveAllNonClusteredIndexes BIT
SELECT
@SourceFileGroupID = groupid
FROM
sysfilegroups
WHERE
groupname = 'PRIMARY'
SELECT
@TargetFileGroupID = groupid
FROM
sysfilegroups
WHERE
groupname = 'NewGroup'
SET @MovePKAndAllUniqueConstraints = 1
SET @MoveAllNonClusteredIndexes = 1
DECLARE @ScriptMsg NVARCHAR(512)
DECLARE @DatabaseName SYSNAME
DECLARE @ServerName SYSNAME
DECLARE @TableHasCI BIT
DECLARE @TableHasIdent BIT
DECLARE @TableHasPK BIT
DECLARE @TableHasUQ BIT
DECLARE @File1Name NVARCHAR(128)
DECLARE @File2Name NVARCHAR(128)
DECLARE @IdentColName NVARCHAR(128)
DECLARE @ColList NVARCHAR(1024)
DECLARE @indid NVARCHAR(128)
DECLARE @Type CHAR(2)
DECLARE @KeyName NVARCHAR(128)
DECLARE @AssocFKeyName NVARCHAR(128)
DECLARE @FKTableName NVARCHAR(128)
DECLARE @CIName NVARCHAR(128)
DECLARE @IsPadIndex BIT
DECLARE @i INT
DECLARE @j INT
DECLARE @SQLStr NVARCHAR(4000)
-- Get server and database names
SET @ServerName = CAST(ISNULL(SERVERPROPERTY('ServerName'), 'Unknown') AS SYSNAME)
SET @DatabaseName = db_name()
-- Some basic verifications:
-- 1. Check that file groups exist, and that the table exists.
SET @File1Name = FILEGROUP_NAME(@SourceFileGroupID)
IF @File1Name IS NULL
BEGIN
IF @SourceFileGroupID IS NULL
SET @SourceFileGroupID = 'NULL'
SET @ScriptMsg = N'The source file group ' + CAST(@SourceFileGroupID AS VARCHAR(64)) + N' does not exist on the database ' + @DatabaseName + N', on server ' + @ServerName + N'. Please provide a valid filegroup id.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
SET @File2Name = FILEGROUP_NAME(@TargetFileGroupID)
IF @File2Name IS NULL
BEGIN
IF @TargetFileGroupID IS NULL
SET @TargetFileGroupID = 'NULL'
SET @ScriptMsg = N'The target file group ' + CAST(@TargetFileGroupID AS VARCHAR(64)) + N' does not exist on the database ' + @DatabaseName + N', on server ' + @ServerName + N'. Please provide a valid filegroup id.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
IF @SourceFileGroupID = @TargetFileGroupID
BEGIN
SET @ScriptMsg = N'The file groups provided are the same. This is not allow in this script.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
-- 2. Check that the target file group is not read-only.
IF FILEGROUPPROPERTY(FILEGROUP_NAME(@TargetFileGroupID), 'IsReadOnly') = 1
BEGIN
SET @ScriptMsg = N'The taget file group (i.e., with file group id = ' + CAST(@TargetFileGroupID AS VARCHAR(32)) + N') is read-only. Aborting table move.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
IF OBJECT_ID('tempdb..#tablesToChange', 'U') IS NOT NULL
DROP TABLE #tablesToChange
SELECT
TABLE_NAME
INTO
#tablesToChange
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME IN
(
SELECT
OBJECT_NAME(id)
FROM
sysindexes i
WHERE
groupid = @SourceFileGroupID
)
--AND TABLE_NAME = 'AssassinObject'
WHILE EXISTS(SELECT * FROM #tablesToChange)
BEGIN
BEGIN TRY
BEGIN TRANSACTION
SELECT
TOP 1
@TableToMove = TABLE_NAME
FROM
#tablesToChange
DELETE
#tablesToChange
WHERE
TABLE_NAME = @TableToMove
PRINT N'Moving table ' + @TableToMove
-- This temp table holds the column names of keys/constraints, and such.
IF OBJECT_ID('tempdb..#tblColTable', 'U') IS NOT NULL
DROP TABLE #tblColTable
CREATE TABLE #tblColTable (
Idx INT IDENTITY(1, 1),
ColName NVARCHAR(128) COLLATE DATABASE_DEFAULT,
IdxOrder CHAR(4))
-- This temp table is used to store the key/constraint properties
-- of the moved table.
IF OBJECT_ID('tempdb..#tblKeysTable', 'U') IS NOT NULL
DROP TABLE #tblKeysTable
CREATE TABLE #tblKeysTable (
Idx INT IDENTITY(1, 1),
KeyName NVARCHAR(128) COLLATE DATABASE_DEFAULT,
indid INT,
Type CHAR(2))
-- This temp table holds the foreign keys of the table.
-- The SQLStmt column is used to build dynamic SQL statements
-- that are related to these foreign keys.
IF OBJECT_ID('tempdb..#tblFKTable', 'U') IS NOT NULL
DROP TABLE #tblFKTable
CREATE TABLE #tblFKTable (
Idx INT IDENTITY(1, 1),
ForeignTableName NVARCHAR(128) COLLATE DATABASE_DEFAULT,
KeyName NVARCHAR(128) COLLATE DATABASE_DEFAULT,
SQLStmt NVARCHAR(1024) COLLATE DATABASE_DEFAULT)
-- This temp table holds the colunms of the foriegn key of the table.
IF OBJECT_ID('tempdb..#tblFKColTable', 'U') IS NOT NULL
DROP TABLE #tblFKColTable
CREATE TABLE #tblFKColTable (
Idx INT IDENTITY(1, 1),
ColName NVARCHAR(128) COLLATE DATABASE_DEFAULT,
FOrP CHAR(1))
SET @TableToMove = LTRIM(RTRIM(@TableToMove))
IF RIGHT(@TableToMove, 1) = ']'
SET @TableToMove = LEFT(@TableToMove, LEN(@TableToMove) - 1)
IF LEFT(@TableToMove, 1) = '['
SET @TableToMove = RIGHT(@TableToMove, LEN(@TableToMove) - 1)
-- Validate the table name and check that it exists in the system catalog.
IF @TableToMove IS NULL OR @TableToMove = ''
BEGIN
SET @ScriptMsg = N'The table name provided in the script is either null or empty, on server '
+ @ServerName + N' and database ' + @DatabaseName
+ N'. Please provide a valid table name.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TableToMove AND TABLE_TYPE = 'BASE TABLE')
BEGIN
SET @ScriptMsg = N'The table name provided in the script is not found in database '
+ @DatabaseName + N', on server ' + @ServerName + N'. Please provide a valid table name.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
-- Check that the table is indeed defined on the source file group.
IF (SELECT TOP 1 groupid FROM sysindexes WHERE id = OBJECT_ID('[' + @TableToMove + ']') and indid IN (0, 1)) <> @SourceFileGroupID
BEGIN
SET @ScriptMsg = N'The table ' + @TableToMove + ' is not found on filegroup ' + CAST(@SourceFileGroupID AS VARCHAR(32))
+ N'. Please provide a valid table name and source file group.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
-- 3. If we have gotten this far, then it is ok to move the table to the
-- requested filegroup.
-- First thing first: Check whether the table has a clustered index.
SET @TableHasCI = OBJECTPROPERTY(OBJECT_ID('[' + @TableToMove + ']'), 'TableHasClustIndex')
-- If not - check whether the table has an identity column.
-- If it does - apply the CI with the new filegroup on the identity column.
-- Once done - remove the CI. If it does not - check whether the table has a primary
-- key and apply the CI there on the new file group, and then drop the CI.
-- If the table does not have an identity column, or a primary key,
-- then a new identity column is created for the table and the CI
-- is applied on it, and then the CI and the identity column are removed.
-- This whole shabang is done in order to make the CI creation as fast as possible.
-- The case where the table does not have a clustered index to begin with implies
-- bad table design, and should not be common anyhow.
IF @TableHasCI = 0
BEGIN
SET @TableHasIdent = OBJECTPROPERTY(OBJECT_ID('[' + @TableToMove + ']'), 'TableHasIdentity')
IF @TableHasIdent = 0
BEGIN
SET @TableHasPK = OBJECTPROPERTY(OBJECT_ID('[' + @TableToMove + ']'), 'TableHasPrimaryKey')
SET @TableHasUQ = OBJECTPROPERTY(OBJECT_ID('[' + @TableToMove + ']'), 'TableHasUniqueCnst')
-- Only if the table has no PK/UQ or clustered index, then create an identity
-- column on it. This new column will hold the CI.
IF @TableHasPK = 0 AND @TableHasUQ = 0
BEGIN
EXEC(N' ALTER TABLE [' + @TableToMove + N'] ADD
[This_Is_My_Ident_Col_Name] BIGINT IDENTITY (1, 1) ')
SET @IdentColName = 'This_Is_My_Ident_Col_Name'
-- Apply the CI on the identity column. We don't create the CI
-- as unique, since the identity column may be non-unique,
-- due to reseeding.
EXEC(N'CREATE CLUSTERED INDEX [This_Is_My_Clsuetered_Index_Name]
ON [' + @TableToMove + N']([' + @IdentColName + '])
ON [' + @File2Name + N']')
-- The table is now moved -> Remove the CI.
EXEC(N'DROP INDEX [' + @TableToMove + N'].[This_Is_My_Clsuetered_Index_Name]')
-- Finally, remove the added identity column
EXEC(N' ALTER TABLE [' + @TableToMove + N']
DROP COLUMN [This_Is_My_Ident_Col_Name] ')
END
ELSE
BEGIN
-- In this case, the table has a PK/UQ, so we might as well
-- apply the CI on the column(s) of the PK/UQ.
-- First, get the column(s) of the PK/UQ.
SELECT @KeyName = CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WITH (NOLOCK)
WHERE TABLE_NAME = @TableToMove
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
IF @@ROWCOUNT = 0
SELECT TOP 1 @KeyName = CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WITH (NOLOCK)
WHERE TABLE_NAME = @TableToMove
AND CONSTRAINT_TYPE = 'UNIQUE'
-- The varialbe @KeyName now holds the name of the PK/UQ
INSERT INTO #tblColTable (ColName, IdxOrder)
SELECT COL_NAME(OBJECT_ID('[' + @TableToMove + ']'), colid),
-- append the DESC/ASC string, based on the ASC/DESC order of the PK columns
CASE WHEN INDEXKEY_PROPERTY(OBJECT_ID('[' + @TableToMove + ']'),
INDEXPROPERTY(OBJECT_ID('[' + @TableToMove + ']'),
@KeyName,
'IndexID'),
keyno,
'IsDescending') = 1
THEN 'DESC'
ELSE 'ASC'
END
FROM sysindexkeys
WHERE id = OBJECT_ID('[' + @TableToMove + ']')
AND indid = INDEXPROPERTY(OBJECT_ID('[' + @TableToMove + ']'), @KeyName, 'IndexID')
IF @@ROWCOUNT > 0
SET @i = 1
SET @ColList = N''
WHILE EXISTS(SELECT * FROM #tblColTable WHERE Idx = @i)
BEGIN
SELECT @ColList = @ColList + N'[' + ColName + N'] ' + IdxOrder + N' ,'
FROM #tblColTable
WHERE Idx = @i
SET @i = @i + 1
END
SET @ColList = LEFT(@ColList, LEN(@ColList) - 1)
-- Now, apply the CI on the primary key columns. The CI is not
-- created as a unique CI, since if the PK/UQ was added with the NOCHECK
-- option, there could be duplicate entries in the PK/UQ.
EXEC(N'CREATE CLUSTERED INDEX [This_Is_My_Clsuetered_Index_Name]
ON [' + @TableToMove + N'](' + @ColList + ')
ON [' + @File2Name + N']')
-- The last command moved the CI (and thus the table), so we
-- can now drop the CI.
EXEC(N'DROP INDEX [' + @TableToMove + N'].[This_Is_My_Clsuetered_Index_Name]')
END
END
ELSE
BEGIN
-- Here, the table originally had an identity. We apply the CI
-- on the identity column, and then remove it.
SELECT @IdentColName = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK)
WHERE TABLE_NAME = @TableToMove
AND COLUMNPROPERTY(OBJECT_ID('[' + @TableToMove + ']'), COLUMN_NAME, 'IsIdentity') = 1
EXEC(N'CREATE CLUSTERED INDEX [This_Is_My_Clsuetered_Index_Name]
ON [' + @TableToMove + N']([' + @IdentColName + '])
ON [' + @File2Name + N']')
-- The table is now moved -> Remove the CI.
EXEC(N'DROP INDEX [' + @TableToMove + N'].[This_Is_My_Clsuetered_Index_Name]')
END
END
ELSE
BEGIN
-- Now, for the big ELSE. The ELSE applies to the case where the
-- table already has a clustered index. Here, we select the name of the
-- existing clustered index, then drop it from the table, and recreate
-- it on the other filegroup (on the same columns and order as was
-- originally defined for the table).
-- If the CI is also a PK/UQ/unique index, then we first check all foreign
-- keys for the PK/UQ/UI, drop them if they exist, drop the PK/UQ/UI
-- then recreate the PK/UQ/UI as CLUSTERED, and then reapply all the
-- foreign keys constraints. If the CI is non-unique (thus is not
-- associated with a PK/UQ/UI), we just drop and recreate it on the
-- target file group.
SELECT @CIName = [name]
FROM sysindexes WITH (NOLOCK)
WHERE id = OBJECT_ID('[' + @TableToMove + ']')
AND indid = 1
DELETE FROM #tblColTable
INSERT INTO #tblColTable (ColName, IdxOrder)
SELECT COL_NAME(OBJECT_ID('[' + @TableToMove + ']'), colid),
-- append the DESC/ASC string, based on the ASC/DESC order of the PK columns
CASE WHEN INDEXKEY_PROPERTY(OBJECT_ID('[' + @TableToMove + ']'),
INDEXPROPERTY(OBJECT_ID('[' + @TableToMove + ']'),
@CIName,
'IndexID'),
keyno,
'IsDescending') = 1
THEN 'DESC'
ELSE 'ASC'
END
FROM sysindexkeys WITH (NOLOCK)
WHERE id = OBJECT_ID('[' + @TableToMove + ']')
AND indid = 1
ORDER BY keyno ASC
SELECT @i = MIN(Idx)
FROM #tblColTable
SET @ColList = N''
WHILE EXISTS(SELECT * FROM #tblColTable WHERE Idx = @i)
BEGIN
SELECT @ColList = @ColList + N'[' + ColName + N'] ' + IdxOrder + N' ,'
FROM #tblColTable
WHERE Idx = @i
SET @i = @i + 1
END
SET @ColList = LEFT(@ColList, LEN(@ColList) - 1)
-- Check whether the clustered index is also the PK, or a unique constraint (UQ),
-- or a unique index (UI) that is neither a PK or a UQ.
-- If the CI is either one of the above, we first check whether any foreign keys
-- reference this PK/UQ/UI. If so - we drop the FKs, then drop the PK/UQ/UI,
-- then recreate the PK/UQ/UI on the target filegroup, and then recreate all
-- the foreign keys dropped earlier.
-- If the CI is other than the above (i.e., it is a non-unique clustered index)
-- then we simply drop it and recreate it on the target filegroup.
IF OBJECTPROPERTY(OBJECT_ID('[' + @CIName + ']'), 'IsPrimaryKey') = 1
OR OBJECTPROPERTY(OBJECT_ID('[' + @CIName + ']'), 'IsUniqueCnst') = 1
OR INDEXPROPERTY(OBJECT_ID('[' + @TableToMove + ']'), @CIName, 'IsUnique') = 1
BEGIN
-- This case stands for a CI which is a PK/UQ/UI.
-- First, we drop all foreign keys associated with the PK/UQ/UI.
-- These FK constraints will be reapplied on the PK later,
-- (i.e., after the PK/UQ/UI is recreated on the target filegroup).
DELETE FROM #tblFKTable
-- Get all the FK constraints associated with the PK/UQ/UI.
-- Here, we query sysreferences so we could get our hands on all the
-- foreign keys that reference the PK/UQ/UI of the table
-- that needs to be moved.
INSERT INTO #tblFKTable (ForeignTableName, KeyName)
SELECT OBJECT_NAME(fkeyid), OBJECT_NAME(constid)
FROM sysreferences WITH (NOLOCK)
WHERE rkeyid = OBJECT_ID('[' + @TableToMove + ']')
AND rkeyindid = INDEXPROPERTY(OBJECT_ID('[' + @TableToMove + ']'), @CIName, 'IndexID')
SELECT @AssocFKeyName = MIN(KeyName)
FROM #tblFKTable
WHILE @AssocFKeyName IS NOT NULL
BEGIN
-- Get the list of primary and then foreign columns
-- for the collected FK constraints. The CASCADE UPDATE,
-- CASCADE DELETE, and NOT FOR REPLICATION properties
-- of the FK are considered later.
SELECT @FKTableName = ForeignTableName
FROM #tblFKTable
WHERE KeyName = @AssocFKeyName
DELETE FROM #tblFKColTable
-- First, the tables of the foreign table. The select is ordered by keyno
-- so the order of columns in the FK will remain unchanged by the
-- drop/recreate operation.
INSERT INTO #tblFKColTable (ColName, FOrP)
SELECT COL_NAME(fkeyid, fkey), 'F'
FROM sysforeignkeys
WHERE constid = OBJECT_ID('[' + @AssocFKeyName + ']')
ORDER BY keyno
-- Similarly, for the primary table.
INSERT INTO #tblFKColTable (ColName, FOrP)
SELECT COL_NAME(rkeyid, rkey), 'P'
FROM sysforeignkeys
WHERE constid = OBJECT_ID('[' + @AssocFKeyName + ']')
ORDER BY keyno
-- We now build the FK creation statement
SELECT @j = MIN(Idx)
FROM #tblFKColTable
SET @SQLStr = N'ALTER TABLE [' + @FKTableName + N'] '
+ N