存储过程如下:
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 INTSELECT @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 ENDIF 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 ENDIF @NewFileGroup IS NULL
SELECT @NewFileGroup = name FROM sys.filegroups WHERE is_default = 1IF 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 ENDDECLARE @sql NVARCHAR(4000) ,
@Enter NVARCHAR(20) , @PrimaryKey sysnameSELECT @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 = @objectidSELECT @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 NULLIF 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 NULLSELECT @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);