Check if an index exists on table column

What you are retrieving is the lowest number of columns used in an index on a given table, where the index includes a given column. Your query can be simplified to:

    SELECT  TOP 1 ColumnsCount = COUNT(*)
    FROM    sys.index_columns AS ic
            INNER JOIN sys.indexes AS i
                ON ic.[object_id] = i.[object_id]
                AND ic.index_id = i.index_id
            INNER JOIN sys.columns AS c
                ON ic.[object_id] = c.[object_id]
                AND ic.column_id = c.column_id
    WHERE   ic.[object_id] = OBJECT_ID(N'dbo.YourTableName')
    AND     i.[type] != 0
    AND     ic.is_included_column = 0
    GROUP BY i.index_id
    HAVING  COUNT(CASE WHEN c.Name = 'YourColumnName' THEN 1 END) > 0
    ORDER BY ColumnsCount;

I've added the condition in ic.is_included_column = 0, on the assumption that you don't want to include non key columns in the account, nor are you interested in indexes where the given column is not a key column. If this assumption is incorrect then remove this predicate.

However, if your current query works, I don't see that there is much benefit from optimising a query on the system catalogs. They aren't likely to be performance killers.


Not sure why such a big query but if I have understood correctly, you are trying to find whether a specific index idx is present in table X. if that's the case then you can directly query sys.indexes table like below (assuming your index name idx123 and your table name is table1)

SELECT * 
FROM sys.indexes 
WHERE name='idx123' 
AND object_id = OBJECT_ID('table1')

what about this its simple and you dont neet to be root user

SHOW INDEXES FROM %TABLE_NAME_% WHERE Column_name = %COLUMN_NAME%;