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, deprecatedsysindexes
system tableadd 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...
精彩评论