Selecting Indexes based on Column/Table Names

Question

Is there a system SP or dmv that allows me to select indexes in my DB based on columns and table names?

What I am trying to do is to drop indexes based on columns with string datatypes as I am in the process of altering my collation settings.

Thanks.

You can try the query on this post... [Query for listing columns, indices and constraints](https://stackoverflow.com/a/51415260/1642932) — Amit Philips, Jul 19 '18 at 05:55

marc_s · Accepted Answer · 2009-07-13T15:43:59.353

You can always determine the indices for a given column and/or table by querying the sys.indexes and sys.index_columns views:

SELECT
    i.Name 'Index Name',
    OBJECT_NAME(i.object_ID) 'Table Name',
    c.Name 'Column Name'
FROM  
   sys.indexes i 
INNER JOIN 
   sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
INNER JOIN
   sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
WHERE 
  c.name = 'Index column name'      -- this for a column name
  -- OBJECT_NAME(i.OBJECT_ID) = 'Table Name'   -- this for table name

Marc

score 1 · Answer 2 · answered Jul 13 '09 at 15:43

you need four system views:

sys.indexes, sys.index_columns, sys.columns and sys.objects. sys.objects contains the name of table, sys.columns. contains object_id of table and type of column, sys.index_columns contains columns id that are present in indexes and index id, sys.indexes contains index id.

edit: yes, sys.objects is not necessary. Tanks @mark.

Selecting Indexes based on Column/Table Names

2 Answers2