January 11, 2010 at 4:22 pm
We need to rename a number of old indexes in a database. But we get an error when we try to rename the indexes using sp_rename:
Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong.
I've searched and found three possible explanations, none of which apply:
* Not pointing to the correct database
* The object name is wrong
* The syntax is wrong
This the syntax we're using:
EXEC sp_rename 'OldName','NewName','INDEX'
We've tried it every which way--fully qualifying "sp_rename", fully qualifying the old name, providing the parameters using variables instead of character strings. We've verified that we're pointing at the correct database, and even tried adding a USE statement prior to the rename statement. We've also copied and pasted the name from the results from sys.indexes.
Anybody got any ideas?
January 11, 2010 at 4:49 pm
This code works fine for me:
IF OBJECT_ID('dbo.IndexRename') IS NOT NULL
DROP TABLE dbo.IndexRename
GO
CREATE TABLE dbo.IndexRename (
ID int NOT NULL
)
CREATE INDEX IDX_IndexRename_ID ON dbo.IndexRename(ID)
GO
sp_rename 'dbo.IndexRename.IDX_IndexRename_ID', 'IDX_IndexRename_ID_Renamed', 'INDEX'
January 11, 2010 at 4:53 pm
I would never in a million years have thought to preface the index name with the table name, but that worked. Thanks!
January 11, 2010 at 4:55 pm
January 11, 2010 at 5:09 pm
I figured there was a forehead slap and a "Doh!" in my near future.
January 11, 2010 at 6:24 pm
chris.mckeever (1/11/2010)
I figured there was a forehead slap and a "Doh!" in my near future.
Welcome to the club. I've been a member for a while ! 😀
January 12, 2010 at 1:29 am
John Rowan (1/11/2010)
Yep, you have to do the same thing when using DROP INDEX too.
However that is deprecated syntax. The newer syntax is
DROP INDEX <index name> ON <Table Name>
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2010 at 1:32 am
chris.mckeever (1/11/2010)
I would never in a million years have thought to preface the index name with the table name, but that worked. Thanks!
The reason you need to is because the index name is not unique in the database. It's only unique by table. Hence you can have several indexes all named (for eg) idx_covering. If you just say rename 'idx_covering' to 'idx_someTable_comecolumn', then which one must be renamed? Hence the prefix with the table name is required to uniquely identify the index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2010 at 10:25 am
GilaMonster (1/12/2010)
John Rowan (1/11/2010)
Yep, you have to do the same thing when using DROP INDEX too.However that is deprecated syntax. The newer syntax is
DROP INDEX <index name> ON <Table Name>
Thanks Gail. I was not aware of that.
I checked my 2005 BOL and it still shows the DROP INDEX <table_name>.<index_name> syntax. So I tried the sytrax that you've given and it works in my 2005 instance. I updated my BOL version to the latest for 2005 and it still shows the old sytax. Odd.
For the record, the new syntax makes much more sense since it is almost identical to the CREATE INDEX syntax.
January 12, 2010 at 11:06 am
John Rowan (1/12/2010)
I checked my 2005 BOL and it still shows the DROP INDEX <table_name>.<index_name> syntax. So I tried the sytrax that you've given and it works in my 2005 instance. I updated my BOL version to the latest for 2005 and it still shows the old sytax. Odd.
That's because you're looking at the 2005 BoL.
From SQL 2008 BoL:
DROP INDEX
{ <drop_relational_or_xml_or_spatial_index> [ ,...n ]
| <drop_backward_compatible_index> [ ,...n ]
}
<drop_relational_or_xml_or_spatial_index> ::=
index_name ON <object>
[ WITH ( <drop_clustered_index_option> [ ,...n ] ) ]
<drop_backward_compatible_index> ::=
[ owner_name. ] table_or_view_name.index_name
and
The syntax defined in <drop_backward_compatible_index> will be removed in a future version of Microsoft SQL Server. Avoid using this syntax in new development work, and plan to modify applications that currently use the feature. Use the syntax specified under <drop_relational_or_xml_index> instead. XML indexes cannot be dropped using backward compatible syntax.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2010 at 11:19 am
Yea, I figured it to be a 2008 thing, but it's odd that the new syntax still works on my 2005 instance.
Thanks again.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply