Post reply

How do we know whether or not a column is involved in an index?

  • March 21, 2013 at 3:20 pm

    #272472

    We have so many indices in a table. Is there any script to easily find whether or not a column is involved in any existing index?

    Many thanks in advance for any input.

  • March 21, 2013 at 3:26 pm

    #1599609

    Check out sys.index_columns.

  • March 21, 2013 at 3:43 pm

    #1599615

    This has limited testing - you can try it.

    SELECT o.name AS ObjName,i.name AS IdxName,i.index_id

    ,c.name AS ColName,ic.column_id AS IxColumnID

    FROM sys.objects o

    INNER JOIN sys.columns c

    ON c.object_id = o.object_id

    LEFT OUTER JOIN sys.indexes i

    INNER JOIN sys.index_columns ic

    ON i.index_id = ic.index_id

    ON o.object_id = i.object_id

    AND i.index_id NOT IN (0,1)

    WHERE OBJECTPROPERTY(c.object_id,'IsMSShipped') = 0

    ORDER BY IdxName DESC

    This will show all columns whether indexed or not. Columns not in indexes will display with null values.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply