Check the existence of Dice?

Question

Is there any way to use a Script check if an index already exists in a table?

I got the following script that generates an index in a table:

CREATE NONCLUSTERED INDEX [meu_indice] ON [dbo].[MinhaTabela]
(
[Id] ASC,
[Chave_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,     DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Only that as I already created the index in the table, if I try to run the script gives error saying that the index has already been created.

Would have some way to check if the index already exists and only create if it does not exist?

Answer 1

You can use a IF thus:

If IndexProperty(Object_Id('MyTable'), 'MyIndex', 'IndexId') Is Null

If there is an Indice will return the ID case will not return Null.

Source: Soen

Answer 2

What I usually do is a select in sys.objects

if (not exists (select null from sys.objects where name = 'meu_indice'))

the complete script gets

if (not exists (select null from sys.objects where name = 'meu_indice'))
    CREATE NONCLUSTERED INDEX [meu_indice] ON [dbo].[MinhaTabela]
    (
    [Id] ASC,
    [Chave_Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,     DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    GO

Answer 3

Use a query in the sysindexes table to verify the existence.

Follow an example:

IF NOT EXISTS(SELECT id FROM sys.sysindexes where name = 'Nome_de_seu_indice')
BEGIN
   EXEC(
   '
    /****** coloque o seu indice aqui dentro do texto
     CREATE UNIQUE NONCLUSTERED INDEX ...
    ...
    ...
')
END;