TSQL Script Index Creation that can be rerun
I want to have a script of indexes that I can append to and rerun as new tables are added to my schema. For this reason, I want to skip creating indexes that already exist, but I have been unable to find a clean way to detect the index is already there. What I want to do is something like this:
IF OBJECT_ID(N'[dbo].[Use开发者_运维百科rs].[IDX_LastName]', '') IS NULL
CREATE INDEX [IDX_LastName] ON [dbo].[Users]
(
[LastName] ASC
)
I think this will do what you need though I'm not sure if there is a more concise way.
IF NOT EXISTS(SELECT * FROM sys.indexes WHERE
name = 'IDX_LastName' and object_id=object_id('[dbo].[Users]'))
CREATE INDEX [IDX_LastName] ON [dbo].[Users] ([LastName] ASC)
精彩评论