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

Question

With SQL Server 2016 when I run this:

SELECT * 
FROM sys.indexes 
WHERE name = 'idx_Parts_PartNum'

There is one result. As expected. But when on the same DB I run:

    exec sp_rename N'idx_Parts_PartNum', N'ux_Parts_PartNum', N'INDEX'

I get this error:

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

What can cause this error given that the first query shows this index name is correct?

I've Googled with no luck. My index name doesn't contain either invalid characters or a reserved word.

Note: this was created as a unique index, not a unique constraint.

I can rename the index using SQL Server Management Studio without error.

Answer 1

You need to give the full path of the index, as shown in the example in the documentation:

CREATE TABLE dbo.YourTable (ID int);
GO

CREATE UNIQUE INDEX IX_YourIndex ON dbo.YourTable (ID);
GO

EXEC sp_rename N'dbo.YourTable.IX_YourIndex',N'UX_YourIndex','INDEX'; --Schema, Table, Index
GO

DROP TABLE dbo.YourTable;

Answer 2

This worked for me , i was missing the table when renaming the index.

USE database ;
GO
EXEC
 sp_rename N'Schema.Table.old_INDEX',
 N'new_INDEX', 
N'INDEX' ;
GO