November 16, 2010 at 10:55 am
When creating an index, we may get an error: the object exists already in the database. How do we know which table the existing index belongs to?
Any input will be greatly appreciated.
November 16, 2010 at 10:57 am
There's a sys.indexes system view you can select from. Use Object_Name() on the ParentObjectID column.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 16, 2010 at 11:06 am
Just a small correction to GSquareds advice, it's the object_id column:SELECT
OBJECT_NAME(object_id) AS TableOrViewName, *
FROM sys.indexes
November 16, 2010 at 11:08 am
Yeah, I confused it for a moment with the sys.columns view. Should have looked before I posted.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 16, 2010 at 11:21 am
However, you are allowed to have the same INDEX name across multiple tables. If you're getting a duplicate index name error, then an index of that name already exists for that table.
However, if what you are trying to add is a PRIMARY KEY or UNIQUE constraint, which are both implemented / enforced by indexes, then those names do have to be unique since those are also objects whose name is in sys.objects, and it must be unique there across the entire database.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply