How do I rename my constraints

Question

I renamed a table in my database with

EXEC sp_rename 'tblOldAndBusted', 'tblNewAndShiny'

and all the foreign key constraints were updated to the new table name, but they're still named based on the old table name. For example, I now have FK_tblOldAndBusted_tblTastyData but I'd like to have FK_tblNewAndShiny_tblTastyData.

Is there an easy way to script this?

Also, am I being too anal? I know the database works fine with the old table name in the constraints, but it feels like broken windows.

user_683319 · Answer

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql?view=sql-server-ver15

If the object to be renamed is a constraint, object_name must be in the form schema.constraint.

So the correct form is:

exec sp_rename 'schema.oldname','newname', 'object'

Prefix the oldname with the schema.

Do not prefix the newname with the schema...

How do I rename my constraints

8 Answers