Rename foreign key constraints (SQL Server 2005 & 2008)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by MadDan » Rename foreign key constraints (SQL Server...

Rename foreign key constraints (SQL Server 2005 & 2008)

Rate Topic

Display Mode

Topic Options

Author

Message

MadDan

Posted Wednesday, January 06, 2010 11:02 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 12, 2012 4:46 PM
Points: 0, Visits: 25

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

Post #843032

Andrew Watson-478275

Posted Tuesday, January 26, 2010 2:52 AM

SSC Eights!

Group: General Forum Members
Last Login: Saturday, May 18, 2013 1:44 AM
Points: 953, Visits: 1,875

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).

Post #853468

ziangij

Posted Tuesday, January 26, 2010 11:53 PM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:53 AM
Points: 1,528, Visits: 359

thanks Andrew, this is better Smile

Post #854183

ziangij

Posted Tuesday, January 26, 2010 11:54 PM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:53 AM
Points: 1,528, Visits: 359

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

Post #854185

shankar08

Posted Friday, February 19, 2010 5:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 12, 2013 8:18 AM
Points: 5, Visits: 51

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 ' '.

Post #868991

Andrew Watson-478275

Posted Friday, February 19, 2010 5:53 AM

SSC Eights!

Group: General Forum Members
Last Login: Saturday, May 18, 2013 1:44 AM
Points: 953, Visits: 1,875

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

Post #868992

shankar08

Posted Friday, February 19, 2010 8:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 12, 2013 8:18 AM
Points: 5, Visits: 51

Yes you're right, thanks its works fine Smile

Post #869143

« Prev Topic | Next Topic »

Permissions