存储过程如下:
USE [SCC_Test_FileGroupAndIndex]
GO
/****** Object: StoredProcedure [dbo].[sp_MoveTable] Script Date: 2015/3/5 11:16:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*移动表数据到别的文件组 2008-12-29*/
ALTER PROC [dbo].[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'无效的表名!'
RAISERROR (N'无效的表名', -- Message text.
10, -- Severity,
1 -- State,
); -- First argument supplies the string.
RETURN
END
IF FILEGROUP_ID(@NewFileGroup) IS NULL
AND @NewFileGroup > ''
BEGIN
-- RAISERROR 50001 N'错误的文件组!'
RAISERROR (N'错误的文件组', -- Message text.
10, -- Severity,
2 -- State,
); -- First argument supplies the string.
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
RAISERROR (@Error, -- Message text.
10, -- Severity.
3 -- State.
);
PRINT N'表' + @objectname + N'数据移动到到文件组' + @NewFileGroup + N' .失败! ';
PRINT N'报错语句:' + @sql;
ROLLBACK TRAN
END CATCH
使用示例:
EXEC sp_MoveTable TblStudents, 'StudentsFileGroup';
报错处理:
由于有时候执行删除索引移动表的时候会报错,报错的语句如下:
ALTER TABLE [TblStudents] DROP CONSTRAINT [PK_TblStudentsIndex] WITH (MOVE TO [Schedule]);
ALTER TABLE [TblStudents] ADD CONSTRAINT [PK_TblStudentsIndex] PRIMARY KEY ([OID] ASC);
可以改成如下语句运行:
ALTER TABLE [TblStudents] DROP CONSTRAINT [PK_TblStudentsIndex];
ALTER TABLE [TblStudents] ADD CONSTRAINT [PK_TblStudentsIndex] PRIMARY KEY ([OID] ASC);
ALTER TABLE [TblStudents] DROP CONSTRAINT [PK_TblStudentsIndex] WITH (MOVE TO [Schedule]);
ALTER TABLE [TblStudents] ADD CONSTRAINT [PK_TblStudentsIndex] PRIMARY KEY ([OID] ASC);