Table의 File Group 옮기기

declare movetable cursor local fast_forward
for
	Select distinct A.name
	From sys.sysobjects AS A 
	INNER JOIN sys.indexes as i on A.id=i.object_id
	INNER JOIN sys.sysindexes AS B ON A.id = B.id
	INNER JOIN sys.sysfilegroups AS C ON B.groupid = C.groupid
	WHERE C.groupid=1 and a.id>100 and b.name is not null and A.xtype='U' 
    and a.uid=1 and i.type=1

open movetable
declare @table varchar(100)
fetch from movetable into @table

while @@fetch_status = 0
begin
	
	declare @sql varchar(1000)
	set @sql=''
	select @sql='CREATE UNIQUE CLUSTERED INDEX ['+name+'] on 
    ['+@table+']( ' from sys.indexes where type=1 and object_id=object_id(@table)

	SELECT @sql=@sql+'['+convert(varchar,c.name)+'] ASC,'
	FROM sys.indexes as i 
	join sys.objects as o on i.object_id=o.object_id
	join sys.index_columns as ic 
    on i.object_id=ic.object_id and i.index_id=ic.index_id
	join sys.columns as c on i.object_id=c.object_id and ic.column_id=c.column_id
	where i.type=1
    and o.schema_id=1
	and o.name =@table
	order by index_column_id

	set @sql=substring(@sql,0,len(@sql))
	set @sql=@sql+' ) WITH (DROP_EXISTING=ON,DATA_COMPRESSION=PAGE) ON FG_EFMS;'

	print @sql

	fetch from movetable into @table
end
close movetable
deallocate movetable

PRIMARY 파일 그룹에 있는 테이블 데이터를 FG_EFMS라는 파일 그룹으로 이동하려고 합니다.

PRINT 된 쿼리를 실행하면, 데이터가 FG_EFMS 파일 그룹으로 이동합니다.

아래 쿼리는 테이블이 어느 FILEGROUP에 있는지 확인

Select A.name
	, C.groupid
	, C.groupname
From sys.sysobjects AS A 
INNER JOIN sys.sysindexes AS B ON A.id = B.id
INNER JOIN sys.sysfilegroups AS C ON B.groupid = C.groupid