How can I get a list of indexes where the first column of the index is displayed first?

Question

I noticed that we have many indexes that start with a specific column, and this column has low power (i.e. the company and 99% of the records belong to a "live" company).

Therefore, these indexes are next to the useless ones (from what I read), since they do not provide a means for dividing the data in the table, which will be found quickly.

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

I have tried various patterns and stuff, but I am not getting the correct results (ie too many rows, rows that contain indexes that do not start with "ROW_COMPANY").

You really appreciate!

Answer 1

Try the following:

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