How can i get a list of Indexes where a particular index column appears first?

Question

I've noticed that we have a lot of indexes that begin with a certain column and that column has low cardinality (i.e. Company and 99% of records belong to the 'live' company).

Therefore these indexes are next to useless (from what I've read) as they are not providing a means of segregating the data in the table to be found quickly.

So, I want to run a script that will find me all indexes in the database, where that indexes first column, is a column called 'ROW_COMPANY' for example.

I've tried various samples and stuff but I'm not getting the correct results (i.e. too many rows, rows that include indexes that do not start with 'ROW_COMPANY').

You Help Appreciated !!

Answer 1

Try this out:

SELECT
    o.name TableName
    , c.name ColumnName
    , i.name IndexName
FROM
    sys.index_columns ic
    INNER JOIN sys.indexes i ON ic.object_id = i.object_id 
                            AND ic.index_id = i.index_id
    INNER JOIN sys.columns c ON ic.object_id = c.object_id 
                            AND ic.column_id = c.column_id
    INNER JOIN sys.objects o ON ic.object_id = o.object_id
WHERE
    o.is_ms_shipped = 0
    AND c.name = 'ROW_COMPANY'
    AND ic.index_column_id = 1