Move a Table to another Filegroup

In case you want to move a table to another filegroup, you would have to change the primary key of the table... and also all the foreign keys to that primary key.

This script allows you to do all this, it drops the constraints of the foreign keys, then it moves the primary key and then it creates again the constraints of the foreign keys.

You can just run the script - as a result you receive the needed SQL statements to move the table (the script itself doesn't make any changes!!).

declare @tablename varchar(50)
set @tablename = --'TableA
declare @newFilegroup varchar(50)
set @newFilegroup = --'FilegroupB'

declare @tableID int
select @tableID = object_id from sys.tables where name=@tablename

select 'BEGIN TRANSACTION'

UNION ALL

select 'ALTER TABLE [dbo].[' + t.name + '] DROP CONSTRAINT [' + fk.name + ']' from sys.tables t
inner join sys.foreign_keys fk on t.object_id = fk.parent_object_id
inner join sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id
inner join sys.tables tMove on fkc.referenced_object_id = tMove.object_id
where tMove.name = @tablename

UNION ALL select 'GO'

UNION ALL

select 'ALTER TABLE [dbo].[' + @tablename + '] DROP CONSTRAINT ' + kc.name + ' WITH (MOVE TO ' + @newFilegroup + ')' from sys.key_constraints kc where kc.parent_object_id = @tableID and kc.type='PK'

UNION ALL select 'GO'

UNION ALL

select 'ALTER TABLE [dbo].[' + @tablename + '] ADD CONSTRAINT ' + kc.name + ' PRIMARY KEY(' + c.name + ') ' from sys.key_constraints kc
inner join sys.columns c on kc.parent_object_id = c.object_id
where kc.parent_object_id = @tableID and c.is_identity = 1 and kc.type='PK'

UNION ALL select'GO'

UNION ALL

select 'ALTER TABLE [dbo].[' + t.name + '] WITH CHECK ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY([' + cMove.name + ']) REFERENCES [dbo].[' + @tablename + '] ([' + c.name + '])' from sys.tables t
inner join sys.foreign_keys fk on t.object_id = fk.parent_object_id
inner join sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id
inner join sys.tables tMove on fkc.referenced_object_id = tMove.object_id
inner join sys.columns c on t.object_id = c.object_id
inner join sys.foreign_keys fkMove on fkMove.object_id=fkc.constraint_object_id
inner join sys.columns cMove on cMove.column_id=fkc.parent_column_id
where tMove.name = @tablename and c.is_identity = 1
and fkc.referenced_object_id=@tableID and fkc.parent_object_id=cMove.object_id

UNION ALL select 'GO'

UNION ALL

select 'ALTER TABLE [dbo].[' + t.name + '] CHECK CONSTRAINT [' + fk.name + ']' from sys.tables t
inner join sys.foreign_keys fk on t.object_id = fk.parent_object_id
inner join sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id
inner join sys.tables tMove on fkc.referenced_object_id = tMove.object_id
where tMove.name = @tablename

UNION ALL

select '--COMMIT'

The result of the script looks like this:

Have fun!