Changin FK constraint Names
-
Hi,
I am trying to write a script which will standardise FK Constraint names, as when different developers have changed tables, the saind names have all changed when the automatic generation of scripts has been used. This is causing me headaches!!. I am using a convention of "FK_" followed by the FKtable followed by the table to which it relates (hope this makes sense). In a few instances, there are two keys joining to the same table, so I need to have a different name for this. I want to add a "1" or something to make it unique. However, my code is not working, as some of the keys do not get changed. Can anyone look at my code, and see the problem, or suggest a better way of doing it? (I'm sure there are loads).
Thanks for any help.
julliff
-
Saw your code in the other thread before it disappeared but try this.
/*
*This should work fine in making sure they are unique and get changed.
*Also this works without a cursor, so should perform a bit better.
*This can even be put into a stored procedure to make it easier to use.
*/
SET NOCOUNT ON -- Don't want count output
/* Create temporary work table. */
CREATE TABLE #tmpTbl (
[fkid] [int] IDENTITY(1,1) NOT NULL,
[CONSTRAINT_NAME] [varchar](128) NOT NULL,
[FOREIGN_KEY_TABLE] [varchar](128) NOT NULL,
[REFERENCE_KEY_TABLE] [varchar](128) NOT NULL,
[OrderId] [int] NOT NULL DEFAULT 1,
)
/* Add records to temp table for items working on. */
INSERT INTO
#tmpTbl
(
CONSTRAINT_NAME,
FOREIGN_KEY_TABLE,
REFERENCE_KEY_TABLE
)
SELECT
OBJECT_NAME(constid) CONSTRAINT_NAME,
OBJECT_NAME(fkeyid) FOREIGN_KEY_TABLE,
OBJECT_NAME(rkeyid) REFERENCE_KEY_TABLE
FROM
sysreferences
ORDER BY
CONSTRAINT_NAME
DECLARE @baseLoop INT
DECLARE @posLoop INT
DECLARE @verifyLoop INT
DECLARE @newVal VARCHAR(128)
DECLARE @oldVal VARCHAR(128)
DECLARE @uniCnt INT
SET @posLoop = 0 --Use this for position thru table.
SET @baseLoop = (SELECT MAX(fkid) FROM #tmpTbl) --@baseLoop is the highest record number in the table
WHILE @posLoop < @baseLoop
BEGIN
SET @posLoop = @posLoop + 1 --Increment @posLoop
SET @uniCnt = 1 --Unique counter reset to one each pass.
SELECT @newVal = 'FK_' + FOREIGN_KEY_TABLE + '_' + REFERENCE_KEY_TABLE FROM #tmpTbl WHERE fkid = @posLoop
SET @verifyLoop = 0
/* This will loop thru al the records to make sure CONSTRAINT_NAME does not exist already.
* Must loop thru to increment @uniCnt properly
*/
WHILE @verifyLoop < @baseLoop
BEGIN
SET @verifyLoop = @verifyLoop + 1
IF @verifyLoop != @posLoop
BEGIN
IF EXISTS (SELECT fkid FROM #tmpTbl WHERE CONSTRAINT_NAME = @newVal + '_' + CAST(@uniCnt AS VARCHAR(5)) AND fkid != @posLoop)
SET @uniCnt = @uniCnt + 1
END
END
--Set the unique order for the CONSTAINT
UPDATE #tmpTbl SET OrderId = @uniCnt WHERE fkid = @posLoop AND OrderID != @uniCnt
--If the CONSTRAINT_NAME doesn't match the new name then change, otherwise don't do again. Saves overall processing.
IF NOT EXISTS(SELECT fkid FROM #tmpTbl WHERE CONSTRAINT_NAME = @newVal + '_' + CAST(OrderId AS VARCHAR(5)) AND fkid = @posLoop)
BEGIN
SELECT @oldVal = CONSTRAINT_NAME, @newVal = @newVal + '_' + CAST(OrderId AS VARCHAR(5)) FROM #tmpTbl WHERE fkid = @posLoop --Get old and new values
SELECT 'Changing "' + @oldVal + '" to "' + @newVal + '"' AS Changing --Output changing message
EXEC sp_rename @oldVal, @newVal, 'OBJECT' --Make name change
END
END
/* Drop our temp table. */
DROP TABLE #tmpTbl
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Hi,
Thanks for speedy reply.
I ran the script and I still got errors. I then ran it another three times, and eventually all of the FKS were renamed. (4 is the maximum number of FKs to the same table!!).
Again, thanks
-
What error did you get if you happen to rememeber so I can test for this and correct the script?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Hi,
Sorry for delay, I had already left the office when your reply was posted.
The tale which comtains the four FKs is called TableColumnDisplay - related to TableColumn.
I have pasted all of the messages re this table below.
As I said rerunning the script decreases the errors, until there are none. I suppose it is something to do with the counter?
Changing "FK__TableColumnDispl__161A357F" to "FK_TableColumnDisplay_TableColumn_1"
Caution: Changing any part of an object name could break scripts and stored procedures.
The OBJECT was renamed to 'FK_TableColumnDisplay_TableColumn_1'.
Changing
-----
Changing "FK__TableColumnDispl__170E59B8" to "FK_TableColumnDisplay_TableColumn_1"
Server: Msg 15335, Level 11, State 1, Procedure sp_rename, Line 332
Error: The @newname value 'FK_TableColumnDisplay_TableColumn_1' is already in use as a OBJECT name and would cause a duplicate that is not permitted.
Changing
-----------------------
Changing "FK__TableColumnDispl__18027DF1" to "FK_TableColumnDisplay_TableColumn_1"
Server: Msg 15335, Level 11, State 1, Procedure sp_rename, Line 332
Error: The @newname value 'FK_TableColumnDisplay_TableColumn_1' is already in use as a OBJECT name and would cause a duplicate that is not permitted.
Changing
----------------------------------------------------------------------------
Changing "FK__TableColumnDispl__18F6A22A" to "FK_TableColumnDisplay_TableColumn_1"
Server: Msg 15335, Level 11, State 1, Procedure sp_rename, Line 332
Error: The @newname value 'FK_TableColumnDisplay_TableColumn_1' is already in use as a OBJECT name and would cause a duplicate that is not permitted.
Again, thanks for taking the time.
julliff
-
Thanks for the bug post, when I get it corrected I will send you an updated copy.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Thanks, I appreciate it
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply