Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rename foreign key constraints (SQL Server 2005 & 2008) Expand / Collapse
Author
Message
Posted Wednesday, January 6, 2010 11:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 25, 2013 12:13 AM
Points: 0, Visits: 27
Comments posted to this topic are about the item Rename foreign key constraints (SQL Server 2005 & 2008)
Post #843032
Posted Tuesday, January 26, 2010 2:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 17, 2014 3:23 AM
Points: 1,253, Visits: 2,227
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
Posted Tuesday, January 26, 2010 11:53 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:40 AM
Points: 2,059, Visits: 370
thanks Andrew, this is better
Post #854183
Posted Tuesday, January 26, 2010 11:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:40 AM
Points: 2,059, Visits: 370
i m wondering, instead of using cursors, we could have used CTE...
Post #854185
Posted Friday, February 19, 2010 5:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 23, 2013 2:23 AM
Points: 5, Visits: 53
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
Posted Friday, February 19, 2010 5:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 17, 2014 3:23 AM
Points: 1,253, Visits: 2,227
You've copied over some unprintable characters with the posted code. Replace all the ' 's with tabs (or some other white space).
Post #868992
Posted Friday, February 19, 2010 8:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 23, 2013 2:23 AM
Points: 5, Visits: 53
Yes you're right, thanks its works fine
Post #869143
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse