Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Marioi
Posting Yak Master
132 Posts |
 Posted - 2005-09-29 : 15:43:06
|
| I am trying to create an index in Enterprise Manager, but I am getting this message: Index 'IX_CreatedBy_User' already exists.The Help says: An index already exists for table '<0s>' with the columns '<1s>'.This index does _not_ exist in the table to which I am adding it, at least according to EM. In an attempt to view indexes as stored, I ran 'select * from sysindexkeys' in Query Analyzer, but I don't know how to read the 93 rows that it returns. I don't see any of the labels I assigned.I have 37 tables and each has a uniqueidentifier-type field named 'GID_CreatedBy_User'.I deleted all indexes named 'IX_CreatedBy_User' in all files. Then I was able to add the index to 2 tables, but I received the same error in the 3rd table.I experienced the same problem earlier while adding indexes on another field, which is varchar(4) type. I started getting this message after creating that index in about 20 tables.Is this an EM bug? Any help will be appreciated. |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-29 : 15:54:14
|
| Sounds like you need to REFRESH EM's viewpoint. Easiest is to close EM and reopen it, and then try again.Its also possible that the name that EM is automatically generating for the index might already have been used for a different index, so manually overriding the Name of the index might help - may sure you haven't already got an index for those columns though, otherwise you'll wind up with two the same!!Kristen |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-09-29 : 16:14:03
|
| Just use this to get a list of indexes on a table:exec sp_help 'Table_Name'CODO ERGO SUM |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2005-09-29 : 16:20:04
|
| Thanks, Kristen. Someone suggested that I name indexes with the file name to make them unique across the DB. Do you do that? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-09-29 : 16:31:16
|
Index names do not have to be unique across the DB, only for the table they are created on.Use sp_help to get a list of existing indexes on a table, and then pick a new name.Constraint names do have to be unique within a DB. Maybe that is what you are running into? For example, a unique constraint, as opposed to a unique index.quote:
Originally posted by Marioi
Thanks, Kristen. Someone suggested that I name indexes with the file name to make them unique across the DB. Do you do that?
CODO ERGO SUM |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2005-09-29 : 16:53:07
|
quote:
Originally posted by Michael Valentine Jones
Constraint names do have to be unique within a DB. Maybe that is what you are running into?
That must be it. I started including the file name and it's fine now.Thank you all - you all really helped me! |
|
|
|
|
|
|
|
|
Index already exists - EM bug?