索引和表的对应

SELECT 
    IndexName=IDX.Name,
    TableName=O.Name
FROM sys.indexes IDX
    INNER JOIN sys.index_columns IDXC
        ON IDX.[object_id]=IDXC.[object_id]
            AND IDX.index_id=IDXC.index_id
    LEFT JOIN sys.key_constraints KC
        ON IDX.[object_id]=KC.[parent_object_id]
            AND IDX.index_id=KC.unique_index_id
    INNER JOIN sys.objects O
        ON O.[object_id]=IDX.[object_id]
    INNER JOIN sys.columns C
        ON O.[object_id]=C.[object_id]
            AND O.type='U'
            AND O.is_ms_shipped=0
            AND IDXC.Column_id=C.Column_id
    INNER JOIN  -- 对于一个列包含多个索引的情况,只显示第1个索引信息
    (
        SELECT [object_id], Column_id, index_id=MIN(index_id)
        FROM sys.index_columns
        GROUP BY [object_id], Column_id
    ) IDXCUQ
        ON IDXC.[object_id]=IDXCUQ.[object_id]
            AND IDXC.Column_id=IDXCUQ.Column_id