TSQL - Get Index By Table Name + Column Name

   · β˜• 1 min read
🏷️
  • #sql
  • #tsql
  • Below SQL code will find an index name that corresponds to the given table name and column name pair, and only include those indices with one column (exclude composite indices). Indices can be changed / added / removed by a DBA without impact to the application (part of DB tuning), so it's best not to assume their naming in an application deployment script. This can be useful to remove / replace certain indices without relying on index name.

    Based on this Stack Overflow's answer:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    
    DECLARE @indexName VARCHAR(256)
    
    WITH indices AS (
        SELECT TableName = MAX(t.name),
               ColumnName = MAX(c.name),
               IndexName = i.name,
               COUNT(*) as FieldCount
          FROM sys.indexes i
          JOIN sys.index_columns ic
            ON i.object_id = ic.object_id and i.index_id = ic.index_id
          JOIN sys.columns c
            ON ic.object_id = c.object_id and ic.column_id = c.column_id
          JOIN sys.tables t
            ON i.object_id = t.object_id 
      GROUP BY i.name
    )
    
    SELECT @indexName = i.IndexName FROM indices i
    WHERE i.TableName = 'Person' --table name filter  
      AND i.ColumnName = 'FirstName' --column name filter
      AND i.FieldCount = 1 --index only contains this column, not a composite index 
    
    SELECT @indexName
    

    Victor Zakharov
    WRITTEN BY
    Victor Zakharov
    Web Developer (Angular/.NET)