Sql-server – Reset Key Names to Defaults

foreign keyprimary-keysql serversql-server-2008-r2ssms

I'm working with some tables/columns that were renamed at some point, but the names of the primary/foreign keys are still based on the previous names.

In SSMS is there any (preferably automatic) way to reset the name of a key to what the default name would be if it were newly created?

Best Answer

There's no way to do this automatically but you can query the catalogue views to find constraint names not in the desired pattern then generate the desired script that way.

Something like

DECLARE @Script NVARCHAR(MAX);

WITH FK
     AS (SELECT *,
                'FK_' + OBJECT_NAME(parent_object_id) + '_' + OBJECT_NAME(referenced_object_id)                  AS provisionalName,
                ROW_NUMBER() OVER (PARTITION BY parent_object_id, referenced_object_id ORDER BY create_date) - 1 AS RN
         FROM   sys.foreign_keys),
     Combined
     AS (SELECT name COLLATE DATABASE_DEFAULT        AS name,
                OBJECT_SCHEMA_NAME(parent_object_id) AS schemaName,
                provisionalName + CASE
                                    WHEN RN = 0 THEN ''
                                    ELSE CAST(RN AS VARCHAR)
                                  END                AS provisionalName
         FROM   FK
         UNION ALL
         SELECT name,
                OBJECT_SCHEMA_NAME(parent_object_id)       AS schemaName,
                type + '_' + OBJECT_NAME(parent_object_id) AS provisionalName
         FROM   sys.key_constraints)
SELECT @Script = x.rename.value('.', 'NVARCHAR(MAX)')
FROM   (SELECT 'exec sys.sp_rename ''' + QUOTENAME(schemaName) + '.' + QUOTENAME(name) + ''', ''' + provisionalName + ''';
'
        FROM   Combined
        WHERE  provisionalName <> name
        FOR XML PATH(''), TYPE) x(rename);

PRINT @Script;
--EXEC (@Script);
Related Question