Rename a Constraint in SQL Server

Rename a constraint in SQL Server?

You can rename using sp_rename using @objtype = 'OBJECT'

This works on objects listed in sys.objects which includes constraints

How to rename primary key constraint in SQL Server

Sometimes you need to explicitly wrap names in square brackets, like this:

sp_rename @objname = N'[Notes].[PK_dbo.Notes]', @newname = N'PK_Notes'

I think it's because of the dot in PK name.

Also, as you see, PK constraints don't need @objtype = 'OBJECT' to be specified.

How do I rename my constraints

Try:

exec sp_rename 'FK_tblOldAndBusted_tblTastyData', 'FK_tblNewAndShiny_tblTastyData', 'object'

Also, there is a bug regarding renaming such things when you deal with non-default schema.

Cannot rename a default constraint for a table in a schema which is not dbo by rsocol @Microsoft Connect

How to rename all system generate constraint names in SQL Server?

Finally, I found the best solution. I used this piece of code:

BEGIN TRANSACTION;
DECLARE @Rename NVARCHAR(MAX);
DECLARE RenameCursor CURSOR FOR
SELECT 'EXEC sp_rename ''[' + c.CONSTRAINT_SCHEMA + '].[' + c.CONSTRAINT_NAME + ']'', ''PK_' + c.TABLE_NAME
       + ''', ''OBJECT'''
FROM TableNameHere.INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
WHERE c.CONSTRAINT_TYPE = 'PRIMARY KEY'
      AND c.TABLE_NAME IS NOT NULL
ORDER BY c.TABLE_NAME;
OPEN RenameCursor;
FETCH NEXT FROM RenameCursor
INTO @Rename;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_executesql @Rename;
    FETCH NEXT FROM RenameCursor
    INTO @Rename;
END;
CLOSE RenameCursor;
DEALLOCATE RenameCursor;
COMMIT TRANSACTION;

You just need to replace TableNameHere with your destination table name. That's all.

How to properly rename all of the unnamed default constraint

Here is the script that worked for me:

select
    CONCAT
    (
        'exec sp_rename ',
        '   @objname = ''['+d.name+']''',
        ' , @newname =  ''DF_',t.name,'_',c.name,   '''',
        ' , @objtype = ''OBJECT'' ;'
    ), 
    t.name, c.name, d.name, definition 
from 
    sys.tables t   
join    
    sys.default_constraints d on d.parent_object_id = t.object_id  
join    
    sys.columns c on c.object_id = t.object_id      
                  and c.column_id = d.parent_column_id
ORDER BY t.name

Just paste the result's 1st column into a new query windows, surround it with BEGIN TRANSACTION and ROLLBACK to test it.

sql:need to change constraint on rename table?

Constraints and indexes will be automatically renamed, but you will need to manually do rename work in stored procedures, triggers, user-defined functions, and views that reference the table. See the documentation on MSDN.

---

Related Topics