Check for index?

Question

Check for index?

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

Is there any way to use a Script that checks whether an index already exists in a table?

I have 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

Would there be any way to check if the index already exists and only create if it does not exist?

sql sql-server

asked by anonymous 26.08.2016 / 14:36

2 answers

1

You can use IF like this:

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

If there is an index it will return ID if it does not return Null .

Source: SoEn

26.08.2016 / 14:37

Some js are printing a strange number on my page Mount an image from other images with PHP

score 1 · Accepted Answer

What I usually do is 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