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%;