I'm trying to change primary key on a table in Azure storage. The plan is to create a new table, move data over and drop the old table. The problem is that I need all the constraints names to be the same as the old ones.

So I can't create a new table until I rename old table and all the constraints on that table. I have tried sp_rename on the constraint, but no luck:

sp_rename 'PK_dbo.ContactSessions', 'PK_dbo.ContactSessions_old', 'OBJECT'

gives me that:

Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.

If I run sp_rename like that:

sp_rename 'PK_dbo.ContactSessions', 'PK_dbo.ContactSessions_old'

I get this error:

No item by the name of 'PK_dbo.ContactSessions' could be found in the current database 'testing_only', given that @itemtype was input as '(null)'.

I have seen this thread: How can I alter an existing Primary Key on SQL Azure? and tried

create clustered index [NEW_INDEX_NAME] on [ContactSessions] ([ContactSessionId]) 
    with (drop_existing=on);

and got the error:

Could not find any index named 'NEW_INDEX_NAME' for table 'ContactSessions'.

Are there other options to change the index name??

I know that is possible because I can right click on the key name in SSMS and rename the key, but I need it in the script.

  • Do you really have the PK named "PK_dbo.ContactSessions"? I believe that it doesn't find it because it sees the part of the name is the schema of the object, while the second is the object name. PS: in SSMS you can get the script of the operation you just did in designer (you have the option to generate to new file).
    – Marian
    Commented Jan 17, 2013 at 14:01
  • @Marian Yes, I need it in that exact format. We are using some other tool (Entity Framework Migrations) to generate our schema. And that tool would be broken with different name for the constraint. I'll try getting the output to the screen - thanks for the tip.
    – trailmax
    Commented Jan 17, 2013 at 14:27
  • For sp_rename, (a) use the object type INDEX, and (b) the new name should be the object name only, without a schema. I don't have Azure handy to test this, but it works fine on a standalone instance.
    – Jon Seigel
    Commented Jan 17, 2013 at 14:37
  • @JonSeigel Run this: sp_rename 'PK_dbo.ContactSessions', 'ContactSessions_old', 'INDEX' and got the same error: "Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong."
    – trailmax
    Commented Jan 17, 2013 at 14:45
  • 2
    If that's the case, you have to delimit the identifier. sp_rename '[schema].[table].[PK_dbo.ContactSessions]' ....
    – Jon Seigel
    Commented Jan 17, 2013 at 14:53

2 Answers 2


It's a good practice to delimit all identifiers. In this particular case, you said the PK_dbo prefix was actually part of the index name. Therefore, the name has to be delimited appropriately, or it cannot be parsed unambiguously.

Also, what's actually being renamed is an index, not an object.

This should work after filling in the blanks:

sp_rename '[<schema>].[<table>].[PK_dbo.ContactSessions]', 'ContactSessions_old', 'INDEX';

I wouldn't use CREATE INDEX ... WITH(DROP_EXISTING = ON) for this because it will physically recreate the index, which is unnecessary in this scenario. This construct is best used for when the definition of the index needs to change while preserving the name.


I had exactly the same problem. For my case, it worked to place the old name between square brackets inside the quotes. So in your case, you might try:

sp_rename '[PK_dbo.ContactSessions]', 'PK_dbo.ContactSessions_old'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.