开发者

get index name within specific table name

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_NORECOM开发者_开发技巧PUTE  = 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,


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.....


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...

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜