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.

Answer 1

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

Answer 2

If there are too many to rename, how about export to dump, edit the dump in any text editor of choice to replace the table name, and then restore from dump? I mean export the dump of only constraints, not all.