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:


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/… Commented Jul 26, 2019 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 Commented Jul 26, 2019 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 Commented Jul 26, 2019 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. Commented Jul 26, 2019 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. Commented Jul 26, 2019 at 20:31

1 Answer 1


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

CREATE PROCEDURE dbo.ImplementNamingStandard
    @SELECT_Only        BIT = 1,
    @ForeignKeys        BIT = 1

    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
        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;

    SELECT @sql;

    IF @SELECT_Only = 0 AND @sql > N''
        EXEC sp_executesql @sql;
  • 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)'. Commented Jul 30, 2019 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. Commented Jul 30, 2019 at 20:31
  • Unfortunately I'm still getting this error message. Commented Jul 30, 2019 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? Commented Jul 30, 2019 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'; Commented Jul 30, 2019 at 21:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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