IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_MoveTableToFileGroup')
	BEGIN
		DROP  Procedure  dbo.proc_MoveTableToFileGroup
	END

GO

CREATE PROCEDURE [dbo].[proc_MoveTableToFileGroup]
(
	@fileGroup sysname,
	@tableName sysname
)
as
begin
	declare @data_space_id int
	declare @object_id int
	declare @index_id int
	declare @index_name sysname
	declare @object_name sysname
	declare @fileGroupName sysname
	declare @index_cols nvarchar(4000)
	declare @sql nvarchar(4000)
	declare @key_ordinal int
	set @index_id = 0
	set @key_ordinal = 0

	select @data_space_id = data_space_id, @fileGroupName = name from sys.filegroups where name = @fileGroup
	if @data_space_id is null
	begin
		raiserror ('The specified filegroup does not exist.', 16, 1)
		return
	end

	while 1=1
	begin
		select top 1
				@object_id = i.object_id, 
				@index_id = i.index_id,
				@index_name = i.name,
				@object_name = o.name
			from 
				sys.indexes AS i
			inner join 
				sys.objects AS o
			ON
				i.object_id = o.object_id
			where 
				i.index_id > 0 and
				o.type = 'U' and
				o.name = @tableName and
				i.index_id > @index_id and
				i.data_space_id <> @data_space_id
			order by i.index_id
			
		if @@rowcount = 0 
		begin
			if @index_id = 0
				print 'There are no indexes to move to filegroup ' + @fileGroupName + ' for table ' + @tableName
			break
		end
			
		set @index_cols = null
		set @key_ordinal = 0

		while 1=1
		begin
			select top 1 
				@index_cols = case when @index_cols is null then '[' + c.name + ']' else @index_cols + ', [' + c.name + ']' end + 
				case when i.is_descending_key = 0 then ' asc' else 'desc' end, 
				@key_ordinal = i.key_ordinal
			from 
				sys.index_columns i 
			inner join 
				sys.columns as c
			on 
				i.object_id = c.object_id and
				i.column_id = c.column_id
			where 
				i.object_id = @object_id and 
				i.index_id = @index_id and 
				i.key_ordinal > @key_ordinal
			order by i.key_ordinal

			if @@rowcount = 0 
				break
		end	

		select @sql = 
				case when i.is_primary_key = 1 then 
						N'begin try ' + 
						N'begin tran ' + 
						N'alter table [' + @object_name + '] drop constraint [' + i.name + '] ' + 
						N'alter table [' + @object_name + '] add constraint [' + i.name + '] ' + 
						'primary key ' +
						case when  i.type = 1 then 'clustered ' else 'nonclustered ' end +
						' (' + @index_cols + ') ' + 
						'with (' +
						'IGNORE_DUP_KEY = ' + case when  i.ignore_dup_key = 1 then 'ON ' else 'OFF ' end +
						', PAD_INDEX = ' + case when  i.is_padded = 1 then 'ON ' else 'OFF ' end +
						case when  i.fill_factor = 0 then '' else ', FILLFACTOR = ' + CONVERT(nvarchar(10),i.fill_factor) end + 
						') ' + 
						'ON [' + @fileGroupName + ']' + 
						N' commit ' + 
						N'end try ' + 
						N'begin catch ' + 
						N'rollback ' + 
						N'end catch ' 
					else 
						N'create ' + 
						case when  i.is_unique = 1 then 'unique ' else '' end +
						case when  i.type = 1 then 'clustered ' else 'nonclustered ' end +
						'index [' + i.name + '] on [' + @object_name + '] (' + @index_cols + ') ' + 
						'with (' +
						'IGNORE_DUP_KEY = ' + case when  i.ignore_dup_key = 1 then 'ON ' else 'OFF ' end +
						', PAD_INDEX = ' + case when  i.is_padded = 1 then 'ON ' else 'OFF ' end +
						case when  i.fill_factor = 0 then '' else ', FILLFACTOR = ' + CONVERT(nvarchar(10),i.fill_factor) end + 
						', DROP_EXISTING = ON ) ' + 
						'ON [' + @fileGroupName + ']'
				end
		from 
			sys.indexes AS i
		where 
			i.object_id = @object_id and
			i.index_id = @index_id

		print 'Moving index ' + @index_name + ' to filegroup ' + @fileGroupName 
		print @sql
		print ''
		exec sp_executesql @sql

	end
end