以前用PD設計數據庫時，或者直接在企業管理器設計表結構時，總想將外鍵手工按規則命名，而不是使用系統自動產生的隨機名稱。但是手工命名一段時間後，就覺得太累，啰嗦，很煩，慢慢的就不了了之了。不過，平心而論我們還是希望所有對象的命名是規范的，這樣做不但看著舒服、顯得好看專業，而且在著急調整某個外鍵時可以根據規則直接寫出其名稱，不需要先去查看到底其名稱是什麼。
今天痛下決心，寫了個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
/*--------------------重命名完成--------------------*/
/*----重復的外鍵定義需要刪除（即，除了外鍵名稱外其余信息完全相同）----*/
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
