get index name within specific table name

Question

I need to check if this index not exist in specific table name not in all tables because this select statement select all indexes under this condition.

IF NOT EXISTS (SELECT name from sysindexes WHERE name = 'IDX_InsuranceID')

CREATE NONCLUSTERED INDEX [IDX_InsuranceID] ON [dbo].[QuoteInsurances] 
(
    [InsuranceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

Thanks,

Answer 1

Your check is okay, I would change two things:

• use the sys.indexes system catalog view (if you're on SQL Server 2005 or newer) instead of the older, deprecated sysindexes system table

• add a check to the object_id (the link to the table) to your query

Something like this:

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IDX_InsuranceID' 
               AND object_id = OBJECT_ID('your-table-name-here'))
   -- do whatever you need to do here.....

Answer 2

If I understand correctly no 2 indexes in the entire database can have the same name, so I think you're safe. If you think I'm incorrect, try to create two indexes on different tables with the same name and see what happens...