Check for index on a column

Question

Is there anyway we can check if a particular column in table has an index or not? I am using SQL Server Management Studio 2016. I did search online but all the solutions talk about looking for an index on the entire table rather than one column.

Answer 1

Yes, you can do this by exploring system views sys.indexes and sys.index_columns.

For example, if you want to find out is there an index on the 'StartDate' column in the table 'Production.BillOfMaterials' in the AdventureWorks database,

you can execute the following query.

DECLARE @colName AS SYSNAME = 'StartDate'
DECLARE @tableName AS NVARCHAR(256) = 'Production.BillOfMaterials'
    
SELECT i.name AS index_name, 
           COL_NAME(ic.object_id, ic.column_id) AS column_name, 
           ic.index_column_id, 
           ic.key_ordinal, 
           ic.is_included_column
    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(@tableName)
          AND COL_NAME(ic.object_id, ic.column_id) = @colName;

… and you will get the result as follows

Based on your comment I created two more examples

First - filtering based on the column name

In the snippet below there is an answer why do you can't get the desired result. Object_Id is schema-based, so passing just table name without schema produces NULL value.

In the example, I'm filtering system views on the AdvantureWorks2016 database, based on the column 'StartDate' '

DECLARE @colName AS SYSNAME = 'StartDate'
--DECLARE @tableName AS NVARCHAR(256) = 'Production.BillOfMaterials'


SELECT i.name [Index Name], 
       OBJECT_SCHEMA_NAME(ic.object_id) + '.' + OBJECT_NAME(ic.object_id) [Table Name],
       COL_NAME(ic.object_id, ic.column_id) [Column Name], 
       ic.index_column_id, 
       ic.key_ordinal, 
       ic.is_included_column
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 COL_NAME(ic.object_id, ic.column_id) = @colName
ORDER BY [Table Name]

---

The result is shown in the image below.

Second- filtering based on the list of tables

If you would like to reduce the scope of the tables. Let's say you know the list of the tables you want to examine, you can use the following snippet.

DECLARE @tables AS TABLE 
(
TableName NVARCHAR(256)
)
INSERT INTO @tables
VALUES 
('HumanResources.EmployeeDepartmentHistory'),
('Production.BillOfMaterials'),
('Production.ProductCostHistory'),
('Production.ProductListPriceHistory')

SELECT i.name [Index Name], 
       OBJECT_SCHEMA_NAME(ic.object_id) + '.' + OBJECT_NAME(ic.object_id) [Table Name],
       COL_NAME(ic.object_id, ic.column_id) [Column Name], 
       ic.index_column_id, 
       ic.key_ordinal, 
       ic.is_included_column
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 EXISTS 
(
SELECT * FROM @tables t WHERE t.TableName = OBJECT_SCHEMA_NAME(ic.object_id) + '.' + OBJECT_NAME(ic.object_id)
)
ORDER BY [Table Name]

And the result is shown in the image below.