Menu
I have a PK constraint on table Notes named PK_dbo.Notes and want to rename it to PK_Notes using SQL Server DDL, i.e. not by using SSMS rename menu option.
Mentioned in another question's answers queries don't work for me. That thread's answers are also helpful, but don't work too.
786
In SQL Server, you can use the sp_rename stored procedure to rename a user created object in the current database, including a primary key. This can be handy if you've got a primary key that had its name automatically assigned, and you now want to give it a more readable name.
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.
Show activity on this post. drop foreign keys from others tables pointing to the primary key you want to rename. rename the primary key. add the foreign column to other tables.
It is not possible to rename a constraint for a column referenced by a view. For more details, see View Dependencies. Tip: This command can be combined with other ALTER TABLE commands in a single statement.
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.
73
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