Menu
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.
655
sp_rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY KEY constraint, the PRIMARY KEY constraint is also automatically renamed by sp_rename . sp_rename can be used to rename primary and secondary XML indexes.
You can use the following SQL query, to get the name of the constraints which are created by the Oracle server on the TEST11 table. SELECT CONSTRAINT_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME='TEST11'; SELECT CONSTRAINT_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME='TEST11';
An existing constraint cannot be modified. To define another column, or set of columns, as the primary key, the existing primary key definition must first be dropped, and then re-created.
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
196
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
