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.