为表切换文件组（sql）
请教为表切换文件组的SQL脚本，谢谢。
详细描述：我已创建表结构，并且也已经创建文件组以及数据文件。由于不能登陆数据库服务器，现在就想指定一个表并且为该表指定文件组，请问能否用脚本实现？多谢！
[最优解释]
-创建数据库时指定数据文件
create database db
on
primary(name=db,
filename='C:\program files\mircosoft SQL server\mssql10.mssqlserver\mssql\data\db.mdf',
size=100MB
maxsize=200
filegrowth=20)
go
--解释下上面的参数：
--primary关键字，指定关联<文件定义>列表用于定义主要数据文件，一个数据库只能有一个主文件，如果没有指定primary,那么create database 语句中列出的第一个文件将成为主文件。
--name关键字，用于指定数据文件的逻辑名称
--filename关键字，指定数据文件的操作系统文件名。其后面的参数是创建数据文件时定义的物理文件的路径名和文件名。
--size关键字，指定数据文件的大小。
--maxsize关键字，指定数据文件可以增长到的最大大小。
--unlimitted关键字，上面的例子中省略掉了。若指定了，则代表指定定义的数据文件将增长到磁盘变满为止。
--filegrowth关键字，指定数据文件的增长增量，其值不能超过maxsize设置。0表示不增长。，默认值为MB。如果指定为%，则增量大小为发生时文件大小的指定百分比，如果没有指定，默认值为10%。
--在创建数据库时指定文件组
create database db
on
primary(name=db1,
filename='C:\program files\mircosoft SQL server\mssql10.mssqlserver\mssql\data\db1.mdf',
size=100MB
maxsize=200
filegrowth=20)，
filegroup filegroup1
(name=db2,
filename='C:\program files\mircosoft SQL server\mssql10.mssqlserver\mssql\data\db2.mdf',
size=100MB
maxsize=200
filegrowth=20)
go
--创建数据库时指定事务日志文件
create database db
on
primary(name=db1,
filename='C:\program files\mircosoft SQL server\mssql10.mssqlserver\mssql\data\db1.mdf',
size=100MB
maxsize=200
filegrowth=20)，
log on(
name=dblog,
filename='C:\program files\mircosoft SQL server\mssql10.mssqlserver\mssql\data\db_log.ldf',
size=10MB
maxsize=20
filegrowth=2)
go
ALTER TABLE 表名 ADD CONSTRAINT 主健名 PRIMARY KEY CLUSTERED
(
列
)ON [文件組]
或
創表時 指定
create table t(ID int) on [文件組]
楼主的意思是用SQL语句?
[其他解释]
不会
友情帮顶..........
[其他解释]
补充：由于数据库已正式使用，不能重新修改和重建表结构，服务器也不是我们管理的，只能用查询分析器连接，有没有那位朋友用脚本为表切换所属文件组。谢谢。。。急急急。。。。
[其他解释]
Use Test
If object_id('sp_MoveTable') Is Not Null
Drop Proc sp_MoveTable
Go
/*移动表数据到别的文件组 2008-12-29*/
Create Proc sp_MoveTable
(
@objectname sysname,
@NewFileGroup sysname=null
)
As
Set Nocount On
Declare @objectid int
Select @objectid=object_id,@objectname=name
From sys.objects As a
Where name=@objectname
And Type='U'
And is_ms_shipped=0
And Not Exists(Select 1
From sys.extended_properties
Where major_id=a.object_id And
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support'
)
If @objectid Is Null
Begin
Raiserror 50001 N'无效的表名!'
Return
End
If filegroup_id(@NewFileGroup) Is Null And @NewFileGroup>''
Begin
Raiserror 50001 N'错误的文件组!'
Return
End
IF @NewFileGroup Is Null
Select @NewFileGroup=name From sys.filegroups Where is_default=1
If Exists(Select 1 From sys.indexes As a Inner Join sys.filegroups As b On b.data_space_id=a.data_space_id Where a.object_id=@objectid And b.name=@NewFileGroup And (a.type=0 Or is_primary_key=1))
Begin
Print N'表'+@objectname+N'已在文件组'+@NewFileGroup+N' .不需要移动! '
Return
End
Declare @sql nvarchar(4000),
@Enter nvarchar(20),
@PrimaryKey sysname
Select @sql='',@Enter=Char(13)+Char(10)
--删除主键、外键、索引
Select @sql=@sql+'Alter Table '+Quotename(object_name(a.parent_object_id))+' Drop Constraint '+Quotename(a.name)+@Enter
From sys.Foreign_keys As a
Where a.referenced_object_id=@objectid
Select @sql=@sql+
Case When b.object_id Is Not Null Then 'Alter Table '+Quotename(@objectname)+' Drop Constraint '+Quotename(a.name)+
Case b.Type When 'PK' Then ' With (Move To '+Quotename(@NewFileGroup)+')' Else '' End
Else 'Drop Index '+Quotename(a.name)+'.'+Quotename(@objectname)
End +@Enter
From sys.indexes As a
Left Outer Join sys.objects As b On b.parent_object_id=a.object_id And b.Type In('PK','UQ') And b.name=a.name
Where a.object_id=@objectid And a.name Is Not Null
If Not Exists(Select * From sys.indexes Where object_id=@objectid And is_primary_key=1)
Begin
Set @PrimaryKey='ID'+Replace(Newid(),'-','')
--创建主键（在表没有主键的情况）
Set @sql=@sql+'Alter Table '+Quotename(@objectname)+' Add '+@PrimaryKey +' uniqueidentifier Not Null ,Constraint DF_'+@objectname+'_'+@PrimaryKey+' Default(newid()) For '+@PrimaryKey+''+
',Constraint PK_'+@objectname+'_'+@PrimaryKey+' Primary Key ('+@PrimaryKey+' Asc)'+@Enter
--删除主键
Set @sql=@sql+'Alter Table '+Quotename(@objectname)+' Drop Constraint PK_'+@objectname+'_'+@PrimaryKey +' With (Move To '+Quotename(@NewFileGroup)+')'+@Enter
Set @sql=@sql+'Alter Table '+Quotename(@objectname)+' Drop Constraint DF_'+@objectname+'_'+@PrimaryKey +@Enter
Set @sql=@sql+'Alter Table '+Quotename(@objectname)+' Drop Column '+@PrimaryKey +@Enter
End
--创建主键、外键、索引
Select @sql=@sql+
Case When b.object_id Is Not Null Then 'Alter Table '+Quotename(@objectname)+' Add Constraint '+Quotename(a.name)+
Case a.is_primary_key When 1 Then ' Primary Key ' Else 'Unique ' End+'('+c.x+')'
Else
'Create Index '+Case a.is_unique When 1 Then 'Unique ' Else '' End+
Case a.type When 1 Then 'Clustered ' Else '' End +
Quotename(a.name)+' On '+Quotename(@objectname)+'('+c.x+')'+ Isnull(' Include('+d.x+')','')
End +@Enter
From sys.indexes As a
Left Outer Join sys.objects As b On b.parent_object_id=a.object_id And b.Type In('PK','UQ') And b.name=a.name
Outer Apply(Select x=Stuff((Select ','+Quotename(y.name)+Case x.is_descending_key When 1 Then ' Desc' Else ' Asc' End
From sys.index_columns As x
Inner Join sys.columns As y On y.object_id=x.object_id And x.column_id=y.column_id
Where x.object_id=a.object_id And x.index_id=a.index_id And x.is_included_column=0
For Xml Path('')
),1,1,'')
) As c
Outer Apply(Select x=Stuff((Select ','+Quotename(y.name)
From sys.index_columns As x
Inner Join sys.columns As y On y.object_id=x.object_id And x.column_id=y.column_id
Where x.object_id=a.object_id And x.index_id=a.index_id And x.is_included_column=1
For Xml Path('')
),1,1,'')
) As d
Where a.object_id=@objectid And a.name Is Not Null
Select @sql=@sql+'Alter Table '+Quotename(object_name(a.parent_object_id))+' Add Constraint '+Quotename(a.name)
+' Foreign Key ('+b.x+') References '+Quotename(@objectname)+'('+c.x+')'+@Enter
From sys.Foreign_keys As a
Outer Apply(Select x=Stuff((Select ','+Quotename(y.name )
From sys.Foreign_key_columns As x
Inner Join sys.columns As y On y.object_id=x.parent_object_id And y.column_id=x.parent_column_id
Where x.constraint_object_id=a.object_id
For Xml Path('')
),1,1,'')
) As b
Outer Apply(Select x=Stuff((Select ','+Quotename(y.name )
From sys.Foreign_key_columns As x
Inner Join sys.columns As y On y.object_id=x.referenced_object_id And y.column_id=x.referenced_column_id
Where x.constraint_object_id=a.object_id
For Xml Path('')
),1,1,'')
) As c
Where a.referenced_object_id=@objectid
--执行脚本
Begin Try
Begin Tran
Exec(@sql)
Commit Tran
Print N'表'+@objectname+N'数据移动到到文件组'+@NewFileGroup+N' .成功! '
End Try
Begin Catch
Declare @Error nvarchar(1024)
Set @Error=ERROR_MESSAGE()
Raiserror 50001 @Error
Print N'表'+@objectname+N'数据移动到到文件组'+@NewFileGroup+N' .失败! '
Rollback Tran
End Catch
Go
Exec sp_MoveTable A ,'MyFileGroup2'
--Select * From sys.filegroups
--Select * From sys.indexes
/*
If object_id('test2') Is Not Null
Drop Table test2
If object_id('test1') Is Not Null
Drop Table test1
Go
Create Table test1
(
id int Identity(1,1) Not Null ,
x nvarchar(50),
Constraint PK_test1_id Primary Key(id Asc)
)
Create nonClustered Index IX_test1_x On Test1(x Asc)
Create Table test2
(
id int Identity(1,1) Not Null,
test1id int not null,
x nvarchar(50),
Constraint PK_test2_id Primary Key(id Asc),
Constraint FK_test2_test1id Foreign Key (test1id) References Test1(id)
)
*/
