Re: unable to edit Index name?

From: Aaron [SQL Server MVP] (ten.xoc_at_dnartreb.noraa)
Date: 07/08/04 

Date: Thu, 8 Jul 2004 14:36:06 -0400

> So the Enterprise Manager can't even handle renaming an index?
> I'm impressed.

I've never been impressed with Enterprise Manager. There are many reasons
it has been completely replaced in the next version. For more of my
complaints, and which functions I *do* use it for, see
http://www.aspfaq.com/2455

> EXEC sp_rename 'VDivision.IX_VDivision', 'Description', 'index'
>
> blew apart with a message about creating a duplicate index....

I didn't have to give the table name as part of the index name. Sounds like
you might actually have an existing index on description? Is it possible
you have two tables called VDivision, one owned by dbo, and one owned by
another owner (or one without an owner, which can happen in obscure
scenarios)? Here is another repro which should prove to you that you
certainly can have multiple indexes with the same name, as long as they are
on different tables, within the same database:

CREATE TABLE dbo.foo
(
 id INT
)
GO

CREATE TABLE dbo.blat
(
 id INT
)
GO

CREATE INDEX blob ON foo(id)
GO
CREATE INDEX blat ON blat(id)
GO

EXEC sp_helpindex foo
EXEC sp_helpindex blat
GO

EXEC sp_rename 'foo.blob', 'blat', 'index'
GO

EXEC sp_helpindex foo
EXEC sp_helpindex blat
GO

DROP TABLE dbo.foo, dbo.blat
GO

And here is a repro which shows how you can get this error if you have two
tables and, if you don't specify the owner name, you might be generating the
index on the wrong table:

CREATE TABLE guest.blat
(
 id INT
)
CREATE TABLE dbo.blat
(
 id INT
)

CREATE INDEX bob ON guest.blat(id)
GO

CREATE INDEX bob ON dbo.blat(id)
GO

CREATE INDEX bob ON blat(id)
GO 

-- 
http://www.aspfaq.com/
(Reverse address to reply.)

Relevant Pages