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