SQL Server数据库自动处理外键关系的存储过程

坏气十足

　　最近总在做数据迁移的工作。做过类似工作的朋友都知道，数据迁移时，表之间的外键约束实在是让你头疼的事。因为你必须要确定表迁移的顺序，否则迁移过程中，总是在报外键约束的错误。即使你把表之间的外键临时drop了，迁移数据后，再create时，就会发现由于数据的不一致，导致外键已经无法创建成功了。
　　根据我的经验，如果迁移的数据不是那么重要的话或者说允许表中有些不一致数据存在的话，那么不必drop和create外键这么麻烦，可以使用no check语句，让外键临时失效，迁移后再启用。这样迁移后的表即使有外键不一致的数据，也不报错。
　　下面这个存储过程是根据表名，schema和操作方式，自动产生外键操作。这里要感谢Greg Robidoux，是他的一篇文章给了我启发，而且下面这个存储过程也是在他原先的存储过程基础上改造了。
　　IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = N'dbo'AND SPECIFIC_NAME = N'USP_TJVictor_PKFK' )DROP PROCEDURE USP_TJVictor_PKFKGO-- =============================================-- Author:TJVictor-- Create date: 2009-12-29-- Description:指定表名和schema后，可产生指定表的外键脚本，-- 操作类型为:ENABLE, DISABLE, DROP, CREATE, DROPANDCREATE-- =============================================CREATE PROCEDURE USP_TJVictor_PKFK@operation VARCHAR(32),  @tableName sysname, @schemaName sysname ASDECLARE @cmd NVARCHAR(max) DECLARE   @FK_NAME sysname,  @FK_OBJECTID INT,  @FK_DISABLED INT,  @FK_NOT_FOR_REPLICATION INT,  @DELETE_RULE    smallint,     @UPDATE_RULE    smallint,     @FKTABLE_NAME sysname,  @FKTABLE_OWNER sysname,  @PKTABLE_NAME sysname,  @PKTABLE_OWNER sysname,  @FKCOLUMN_NAME sysname,  @PKCOLUMN_NAME sysname,  @CONSTRAINT_COLID INT  DECLARE cursor_fkeys CURSOR FOR   SELECT  Fk.name,  Fk.OBJECT_ID,   Fk.is_disabled,   Fk.is_not_for_replication,   Fk.delete_referential_action,   Fk.update_referential_action,   OBJECT_NAME(Fk.parent_object_id) AS Fk_table_name,   schema_name(Fk.schema_id) AS Fk_table_schema,   TbR.name AS Pk_table_name,   schema_name(TbR.schema_id) Pk_table_schema  FROM    sys.foreign_keys Fk LEFT OUTER JOIN   sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id --inner join   WHERE   TbR.name = @tableName  AND schema_name(TbR.schema_id) = @schemaName  OPEN cursor_fkeys  FETCH NEXT FROM   cursor_fkeys   INTO @FK_NAME,@FK_OBJECTID,  @FK_DISABLED,  @FK_NOT_FOR_REPLICATION,  @DELETE_RULE,     @UPDATE_RULE,     @FKTABLE_NAME,  @FKTABLE_OWNER,  @PKTABLE_NAME,  @PKTABLE_OWNER  WHILE @@FETCH_STATUS = 0   BEGIN   -- create statement for enabling FK  IF @operation = 'ENABLE'   BEGIN  SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME   + ']  CHECK CONSTRAINT [' + @FK_NAME + ']'  PRINT @cmd  END  -- create statement for disabling FK  IF @operation = 'DISABLE'  BEGIN     SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME   + ']  NOCHECK CONSTRAINT [' + @FK_NAME + ']'  PRINT @cmd  END  -- create statement for dropping FK and also for recreating FK  IF @operation = 'DROP' OR @operation = 'DROPANDCREATE'BEGIN  -- drop statement  SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME   + ']  DROP CONSTRAINT [' + @FK_NAME + ']'     PRINT @cmd  ENDIF @operation = 'CREATE' OR @operation = 'DROPANDCREATE'BEGIN -- create process  DECLARE @FKCOLUMNS VARCHAR(1000), @PKCOLUMNS VARCHAR(1000), @COUNTER INT  -- create cursor to get FK columns  DECLARE cursor_fkeyCols CURSOR FOR   SELECT  COL_NAME(Fk.parent_object_id, Fk_Cl.parent_column_id) AS Fk_col_name,   COL_NAME(Fk.referenced_object_id, Fk_Cl.referenced_column_id) AS Pk_col_name  FROM    sys.foreign_keys Fk LEFT OUTER JOIN   sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id INNER JOIN   sys.foreign_key_columns Fk_Cl ON Fk_Cl.constraint_object_id = Fk.OBJECT_ID   WHERE   TbR.name = @tableName  AND schema_name(TbR.schema_id) = @schemaName  AND Fk_Cl.constraint_object_id = @FK_OBJECTID -- added 6/12/2008  ORDER BY Fk_Cl.constraint_column_id  OPEN cursor_fkeyCols  FETCH NEXT FROM    cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME  SET @COUNTER = 1  SET @FKCOLUMNS = ''  SET @PKCOLUMNS = ''  WHILE @@FETCH_STATUS = 0   BEGIN   IF @COUNTER > 1   BEGIN  SET @FKCOLUMNS = @FKCOLUMNS + ','  SET @PKCOLUMNS = @PKCOLUMNS + ','  END  SET @FKCOLUMNS = @FKCOLUMNS + '[' + @FKCOLUMN_NAME + ']'  SET @PKCOLUMNS = @PKCOLUMNS + '[' + @PKCOLUMN_NAME + ']'  SET @COUNTER = @COUNTER + 1  FETCH NEXT FROM    cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME  END  CLOSE cursor_fkeyCols   DEALLOCATE cursor_fkeyCols   -- generate create FK statement  SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + ']  WITH ' +   CASE @FK_DISABLED   WHEN 0 THEN ' CHECK '  WHEN 1 THEN ' NOCHECK '  END +  ' ADD CONSTRAINT [' + @FK_NAME   + '] FOREIGN KEY (' + @FKCOLUMNS   + ') REFERENCES [' + @PKTABLE_OWNER + '].[' + @PKTABLE_NAME + '] ('   + @PKCOLUMNS + ') ON UPDATE ' +   CASE @UPDATE_RULE   WHEN 0 THEN ' NO ACTION '  WHEN 1 THEN ' CASCADE '   WHEN 2 THEN ' SET NULL '   END + ' ON DELETE ' +   CASE @DELETE_RULE  WHEN 0 THEN ' NO ACTION '   WHEN 1 THEN ' CASCADE '   WHEN 2 THEN ' SET NULL '   END + '' +  CASE @FK_NOT_FOR_REPLICATION  WHEN 0 THEN ''  WHEN 1 THEN ' NOT FOR REPLICATION '  END  PRINT @cmd  END  FETCH NEXT FROM    cursor_fkeys   INTO @FK_NAME,@FK_OBJECTID,  @FK_DISABLED,  @FK_NOT_FOR_REPLICATION,  @DELETE_RULE,     @UPDATE_RULE,     @FKTABLE_NAME,  @FKTABLE_OWNER,  @PKTABLE_NAME,  @PKTABLE_OWNER  END  CLOSE cursor_fkeys   DEALLOCATE cursor_fkeys  GO
　　使用方法：指定表名，schema（一般是dbo）和操作(ENABLE, DISABLE, DROP, CREATE, DROPANDCREATE)后，存储过程会根据操作，自动产生对应的代码，比如USP_TJVictor_PKFK 'DISABLE','TestTable','dbo'，那么存储过程会自动打印出禁用dbo.TestTable外键的sql语句。
　　如需转载，请注明本文原创自CSDN TJVictor专栏：http://blog.csdn.net/tjvictor