Post reply

Rename foreign key constraints (SQL Server 2005 & 2008)

  • January 6, 2010 at 11:02 am

    #215206

    Comments posted to this topic are about the item Rename foreign key constraints (SQL Server 2005 & 2008)

  • January 26, 2010 at 2:52 am

    #1108520

    Surely we don't really need the dynamic SQL in there, we can just use:

    declare @newName sysname

    ...

    set @newName = @childname + '_' + @parentname + '_' + CAST(@soid as varchar(20)) + '_FK'

    exec sp_rename @name, @newName

    ...

    (And we don't really need the variable I added, it's just a bit neater).

    We probably also want to have some kind of check for long table names, since sysname is "only" 128 characters (or less in earlier versions of SQL).

  • January 26, 2010 at 11:53 pm

    #1109117

    thanks Andrew, this is better 🙂

  • January 26, 2010 at 11:54 pm

    #1109119

    i m wondering, instead of using cursors, we could have used CTE...

  • February 19, 2010 at 5:47 am

    #1121758

    USE DatabaseName

    DECLARE curFK CURSOR FAST_FORWARD FOR

    SELECT so.id,

    so.name,

    child.name as ChildName,

    parent.name as ParentName

    FROM SysForeignKeys sfk

    INNER JOIN SysObjects so ON sfk.constid = so.id

    INNER JOIN SysObjects child ON sfk.fkeyid = child.id

    INNER JOIN SysObjects parent ON sfk.rkeyid = parent.id

    WHERE so.name <> child.name + '_' + parent.name + '_'

    + CAST(so.id AS VARCHAR(20)) + '_FK'

    DECLARE @soid BIGINT, @name VARCHAR(1000), @childname VARCHAR(1000),

    @parentname VARCHAR(1000), @sql NVARCHAR(4000)

    OPEN curFK

    FETCH NEXT FROM curFK INTO @soid, @name, @childname, @parentname

    WHILE @@FETCH_STATUS = 0

    BEGIN

    ????SET @sql = 'exec sp_rename ' + char(39) + @name + char(39) + ', ' +

    ???? CHAR(39) + @childname + '_' + @parentname + '_' +

    ???? CAST(@soid as varchar(20)) + '_FK'+ char(39)

    ????PRINT @sql

    ????EXECUTE sp_executesql @sql

    ????FETCH NEXT FROM curFK INTO @soid, @name, @childname, @parentname

    END

    CLOSE curFK

    DEALLOCATE curFK

    I am getting below listed error on above mention query. using SQL Server 2005

    Incorrect syntax near the keyword 'CLOSE'.

    Incorrect syntax near '?'.

  • February 19, 2010 at 5:53 am

    #1121759

    You've copied over some unprintable characters with the posted code. Replace all the '?'s with tabs (or some other white space).

  • February 19, 2010 at 8:42 am

    #1121893

    Yes you're right, thanks its works fine:-)

  • May 19, 2016 at 1:29 pm

    #1880373

    Thanks for the script.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply