SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rename foreign key constraints (SQL Server 2005 & 2008)


Rename foreign key constraints (SQL Server 2005 & 2008)

Author
Message
MadDan
MadDan
Forum Newbie
Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)

Group: General Forum Members
Points: 0 Visits: 27
Comments posted to this topic are about the item Rename foreign key constraints (SQL Server 2005 & 2008)
Andrew Watson-478275
Andrew Watson-478275
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2221 Visits: 2756
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).
ziangij
ziangij
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3626 Visits: 377
thanks Andrew, this is better :-)
ziangij
ziangij
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3626 Visits: 377
i m wondering, instead of using cursors, we could have used CTE...
shankar08
shankar08
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 57
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 ' '.
Andrew Watson-478275
Andrew Watson-478275
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2221 Visits: 2756
You've copied over some unprintable characters with the posted code. Replace all the ' 's with tabs (or some other white space).
shankar08
shankar08
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 57
Yes you're right, thanks its works fine:-)
Iwas Bornready
Iwas Bornready
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22816 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search