今天痛下决心，写了个sql，按规则重命名所有外键信息。呵呵，挺好，能够懒点的话，还是懒点好！
CREATE PROC proc_Rename_Fkeys
AS
/*************************************************************************
创建时间：2010-01-21 10:31
创建人员：王召冠
功能说明：按照规则重命名所有外键名称。
命名规则：fk__FkTableName__FkColumnName__PkTableName__PkColumnName
注意事项：
业务注释：
--------------------------------------------------------------------------
修改时间：2010-01-21 13:42
修改人员：王召冠
修改说明：增加对多字段主外键关联情况的支持
**************************************************************************/
DECLARE @vTableName NVARCHAR(50), --用户表名称
@vPkTableName NVARCHAR(50), --主键表名称
@vPkColumnName NVARCHAR(50), --主键字段名称
@vFkTableName NVARCHAR(50), --外键表名称
@vFkColumnName NVARCHAR(50), --外键字段名称
@vFkName NVARCHAR(500), --原外键名称
@vFkNewName NVARCHAR(500) --按照规则产生的新外键名称
-- 循环所有用户表
DECLARE table_cursor CURSOR FOR
SELECT NAME
FROM sysobjects
WHERE TYPE = 'U'
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @vTableName
WHILE @@FETCH_STATUS = 0
BEGIN
-- 定义暂存关联表，用来存储当前表的所有关联外键信息（当前表为主键表）
CREATE table #fkeysout
(
OID INT IDENTITY,
PKTABLE_QUALIFIER sysname collate database_default NULL,
PKTABLE_OWNER sysname collate database_default NULL,
PKTABLE_NAME sysname collate database_default NOT NULL,
PKCOLUMN_NAME sysname collate database_default NOT NULL,
FKTABLE_QUALIFIER sysname collate database_default NULL,
FKTABLE_OWNER sysname collate database_default NULL,
FKTABLE_NAME sysname collate database_default NOT NULL,
FKCOLUMN_NAME sysname collate database_default NOT NULL,
KEY_SEQ smallint NOT NULL,
UPDATE_RULE smallint NULL,
DELETE_RULE smallint NULL,
FK_NAME sysname collate database_default NULL,
PK_NAME sysname collate database_default NULL,
DEFERRABILITY smallint NULL
)
-- 取得当前表的关联外键信息
INSERT INTO #fkeysout (
PKTABLE_QUALIFIER,
PKTABLE_OWNER,
PKTABLE_NAME,
PKCOLUMN_NAME,
FKTABLE_QUALIFIER,
FKTABLE_OWNER,
FKTABLE_NAME,
FKCOLUMN_NAME,
KEY_SEQ,
UPDATE_RULE,
DELETE_RULE,
FK_NAME,
PK_NAME,
DEFERRABILITY
)
EXEC sp_fkeys @vTableName
-- 取得正确的，应该修改的外键信息，暂存（因为存在相同的外键，却创建了两个外键约束）
SELECT MIN(OID) AS OID
INTO #tmp1
FROM #fkeysout
GROUP BY PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_NAME, FKCOLUMN_NAME, KEY_SEQ
-- 取得重复的外键信息
SELECT FKTABLE_NAME, FK_NAME, OID
INTO #tmp2
FROM #fkeysout
WHERE OID NOT IN (SELECT OID FROM #tmp1)
/*----------------重命名外键信息-------------------*/
DECLARE fkRename_cursor CURSOR FOR
SELECT PKTABLE_NAME, FKTABLE_NAME, FK_NAME
FROM #fkeysout
WHERE OID IN (SELECT OID FROM #tmp1)
GROUP BY PKTABLE_NAME, FKTABLE_NAME, FK_NAME
OPEN fkRename_cursor
FETCH NEXT FROM fkRename_cursor
INTO @vPkTableName, @vFkTableName, @vFkName
WHILE @@FETCH_STATUS = 0
BEGIN
-- 处理多个字段为主外键的情况
SET @vFkColumnName = ''
SET @vPkColumnName = ''
SELECT @vFkColumnName = @vFkColumnName + '__' + FKCOLUMN_NAME,
@vPkColumnName = @vPkColumnName + '__' + PKCOLUMN_NAME
FROM #fkeysout
WHERE FK_NAME = @vFkName
ORDER BY KEY_SEQ
SET @vFkNewName = 'fk__' + @vFkTableName + @vFkColumnName + '__' + @vPkTableName + @vPkColumnName
EXEC sp_rename @vFkName, @vFkNewName
FETCH NEXT FROM fkRename_cursor
INTO @vPkTableName, @vFkTableName, @vFkName
END
CLOSE fkRename_cursor
DEALLOCATE fkRename_cursor
/*--------------------重命名完成--------------------*/
/*----重复的外键定义需要删除（即，除了外键名称外其余信息xx相同）----*/
DECLARE fkFault_cursor CURSOR FOR
SELECT FKTABLE_NAME, FK_NAME
FROM #tmp2
OPEN fkFault_cursor
FETCH NEXT FROM fkFault_cursor
INTO @vFkTableName, @vFkName
WHILE @@FETCH_STATUS = 0
BEGIN
--ALTER TABLE RM_tToolApply DROP CONSTRAINT
EXEC('ALTER TABLE ' + @vFkTableName + ' DROP CONSTRAINT ' + @vFkName)
FETCH NEXT FROM fkFault_cursor
INTO @vFkTableName, @vFkName
END
CLOSE fkFault_cursor
DEALLOCATE fkFault_cursor
/*--------------- 删除重复的外键 结束 -------------------------*/
DROP TABLE #tmp1
DROP TABLE #tmp2
DROP TABLE #fkeysout
-- 操作下一个用户表的外键信息
FETCH NEXT FROM table_cursor INTO @vTableName
END
CLOSE table_cursor
DEALLOCATE table_cursor
