0

I have a database that was recently migrated from Oracle to SQL Server 2017. Whoever initially setup the database set all the foreign keys to be system generated names. In other words if I look at the foreign keys in a table they are named (SYS_C005214, SYS_C005271, SYS_C005272). I would prefer to have these all renamed to:

FK_ChildTable_childColumn_ParentTable_parentColumn

I would like to setup a script that will change all of the foreign keys to the approprite name. Is there a way to do this without having to go into each key to see it's definition and then renaming it by hand?

Note: I now realize after running Aaron Bertrand's script, I also need this for primary keys.

  • 2
    There isn't a magic button, but there is a script I wrote to help: mssqltips.com/sqlservertip/2709/… – Aaron Bertrand Jul 26 at 16:53
  • Not sure why you want them named to nothing, I would suggest a small script that determine the definition, drop the constraint and then create a new one according to your naming standard – Lennart Jul 26 at 16:58
  • 1
    @Arons link is very usefull. I'm a bit puzzled why default values is concidered constraints in sql server, but thats a side note – Lennart Jul 26 at 17:10
  • 1
    @Aron, that is sort of my point. A rule or constraint (IMO) is something that defines a line between what is permitted and what is not. A default (constraint) is something that contributes when something is missing. To me thats a different mechanism. – Lennart Jul 26 at 18:10
  • 1
    Its a side note, so we should probably agree to disagree, but not null is a constraint, default is not (once again IMO). Though not common, it is possible to have a nullable column with a default value. – Lennart Jul 26 at 20:31
2

Stealing from myself (please see the caveats and comments on that article):

CREATE PROCEDURE dbo.ImplementNamingStandard
    @SELECT_Only        BIT = 1,
    @ForeignKeys        BIT = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX), @cr CHAR(2);
    SELECT @sql = N'', @cr = CHAR(13) + CHAR(10);

    DECLARE @TableLimit TINYINT, @ColumnLimit TINYINT;
    SELECT @TableLimit = 24, @ColumnLimit = 10;

    IF @ForeignKeys = 1
    BEGIN
        SELECT @sql = @sql + @cr + @cr + N'/* ---- Foreign Keys ---- */' + @cr;
        SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' 
          + QUOTENAME(SCHEMA_NAME(schema_id)) + '.'
          + REPLACE(name, '''', '''''') + ''', @newname = N''FK_' 
          + LEFT(REPLACE(OBJECT_NAME(parent_object_id), '''', ''), @TableLimit) + '_' 
          + LEFT(REPLACE(OBJECT_NAME(referenced_object_id), '''', ''), @TableLimit) + ''';'
        FROM sys.foreign_keys
        WHERE is_ms_shipped = 0;
    END

    SELECT @sql;

    IF @SELECT_Only = 0 AND @sql > N''
    BEGIN
        EXEC sp_executesql @sql;
    END
END
GO
  • Now I'm getting an error when I run these. Sp_rename is expecting @itemtype. Msg 15225, Level 11, State 1, Procedure sp_rename, Line 418 [Batch Start Line 2] No item by the name of 'SYS_C005169' could be found in the current database 'foodb', given that @itemtype was input as '(null)'. – Geoff Dawdy Jul 30 at 19:31
  • @Geoff I don't think that was with a foreign key, probably something else that got scooped up but not tested. Let me simplify. Note that this can still fail if you have two foreign keys on one table that reference the same parent table, but it would be with a different error message. – Aaron Bertrand Jul 30 at 20:31
  • Unfortunately I'm still getting this error message. – Geoff Dawdy Jul 30 at 21:17
  • @Geoff You'll have to let us reproduce by creating the object that contains this SYS_C005169 constraint, and the object it points to, in a fiddle or with a full CREATE TABLE script. I don't think adding , @objtype = N'object' is going to resolve that error. Is it possible the constraint (or one of the tables) has a name that needs to be QUOTENAME()d? Can you run again with @SELECT_Only = 1 and post the portion of the resulting script that contains that name? – Aaron Bertrand Jul 30 at 21:33
  • I have a feeling it's because most of the current objects exist in a schema other than dbo. Do I need to do anything special to rename an object in the exec statement? EXEC sp_rename @objname = N'SYS_C005243', @newname = N'FK_FOO_FOO_FOO'; – Geoff Dawdy Jul 30 at 21:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.