SQL Server 2017 – How to Rename Foreign Keys En Masse

foreign keysql serversql-server-2017

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.

Best Answer

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
Related Question