CODEWITHSUNDEEP
sqlsql-serverdatabaseindexing
MaxRecursion
MaxRecursion

Reputation: 4903

How to find index exists on multiple columns

I have delta script SQL query where in I have to check if a specific index is there or not, if not then create one.

e.g. Table structure:

tableA
Col1 int
Col2 varchar
Col3 varchar
Col4 DateTime

And query is: 

IF EXISTS (SELECT 1  
            FROM sys.indexes AS i  
            INNER JOIN sys.index_columns AS ic   
            ON i.object_id = ic.object_id 
                     AND i.index_id = ic.index_id  
                     WHERE i.object_id =OBJECT_ID('dbo.tableA') 
                     AND COL_NAME(ic.object_id,ic.column_id) = 'Col2' ) 
BEGIN
    PRINT 'Index Exists!'
END
ELSE
BEGIN
              PRINT 'Nonclustered does not Exists!'
              IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_tableA_Col2_Col3') 
              BEGIN
              PRINT 'Creating index on tableA'
              CREATE NONCLUSTERED INDEX [IX_tableA_Col2_Col3] ON [dbo].[tableA]
              (
                  [Col2] ASC,
                  [Col3] 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) ON [PRIMARY]
              END
END

There query is able to check if there is index on Col2 or not but here what I want to achieve is check if there is an index created on Col2 and Col3 if not then create.

How can I do that?

Upvotes: 0

Views: 195

Answers (1)

Michał Turczyn
Michał Turczyn

Reputation: 37460

Try this query, it will return index_id of index, that is created on both of specified columns:

declare @tblName varchar(20) = 'yourTable',
        @col1 varchar(20) = 'col1',
        @col2 varchar(20) = 'col2';

select index_id from (
    select index_id,
           (select name 
            from sys.columns 
            where object_id = ic.object_id and column_id = ic.column_id
              and name in (@col1, @col2)) name
    from sys.index_columns ic
    where object_name(object_id) = @tblName
) a group by index_id
having count(*) = 2

Upvotes: 2

Related Questions