SQL server查看各表的索引(sql语句大全)

SELECT
    TableName = t.name,
    IndexName = ind.name,
    ind.type_desc,
    ind.is_unique,
    ind.is_primary_key,
    ColumnNames =
        stuff(
            (
                select ', ' + col.name +
                case when ic.is_descending_key = 1 then ' desc' else '' end
                from sys.index_columns ic
                    inner join sys.columns col on ic.object_id = col.object_id and ic.column_id = col.column_id
                where
                    ic.object_id = ind.object_id
                    and ic.index_id = ind.index_id
                order by
                    ic.index_column_id
                for xml path ('')
            ),1,2,''
        )
FROM
    sys.indexes ind
    INNER JOIN sys.tables t ON ind.object_id = t.object_id
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
    ind.name IS NOT NULL
ORDER BY
    t.name, ind.name;

SELECT
    TableName = t.name,
    IndexName = ind.name,
    ind.type_desc,
    ind.is_unique,
    ind.is_primary_key,
    ColumnNames =
        stuff(
            (
                SELECT ', ' + col.name +
                CASE WHEN ic.is_descending_key = 1 THEN ' desc' ELSE '' END
                FROM sys.index_columns ic
                    INNER JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
                WHERE
                    ic.object_id = ind.object_id
                    AND ic.index_id = ind.index_id
                ORDER BY
                    ic.index_column_id
                FOR XML PATH ('')
            ),1,2,''
        )
FROM
    sys.indexes ind
    INNER JOIN sys.tables t ON ind.object_id = t.object_id
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.name IN ('Table1', 'Table2', 'Table3') -- 替换成你要查询的多个表名，用逗号分隔
ORDER BY
    t.name, ind.name;