CODEWITHSUNDEEP
sqlsql-server
Ehsan HP
Ehsan HP

Reputation: 492

Rename SQL Server Index in MS Sql Server

I want to rename index in sales schema by this script:

EXEC sp_rename N'sales.table.IX1', N'IX2', N'INDEX';

but I get this error:

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

How can I solve it?

Upvotes: 9

Views: 16070

Answers (4)

Andrei
Andrei

Reputation: 44600

I spent a few more minutes than necessary on this one, because of stupid mistakes:

EXEC sp_rename N'SCHEMA.TABLE.OLD_INDEX_NAME', N'NEW_INDEX_NAME', N'index';

Note: Don't change N'index', it must stay like this to let the procedure know that you're modifying an index object. Link to the documentation.

Upvotes: 2

abdelgrib
abdelgrib

Reputation: 1140

Add schema to table name (brackets are not mandatory)

exec sp_rename N'[schema].[table].IX_index', N'IX_index_new', N'INDEX';

Upvotes: 0

Mohammed Osman
Mohammed Osman

Reputation: 4236

I applied this SQL syntax and it works well with me.

EXEC sp_rename N'sales.table.IX1', N'sales.table.IX2', N'INDEX';

Upvotes: 0

Roman Marusyk
Roman Marusyk

Reputation: 24589

Try to remove schema name if you do not have the same table in other schema

EXEC sp_rename N'table.IX1', N'IX2', N'INDEX';

Upvotes: 6

Related Questions