所有索引列表 &SQL Server DB 中的索引列

2022-01-31 00:00:00 indexing reverse-engineering tsql sql-server

如何获取所有索引的列表 &SQL Server 2005+ 中的索引列?我能得到的最接近的是:

How do I get a list of all index & index columns in SQL Server 2005+? The closest I could get is:

select s.name, t.name, i.name, c.name from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id
inner join sys.columns c on c.object_id = t.object_id and
        ic.column_id = c.column_id

where i.index_id > 0    
 and i.type in (1, 2) -- clustered & nonclustered only
 and i.is_primary_key = 0 -- do not include PK indexes
 and i.is_unique_constraint = 0 -- do not include UQ
 and i.is_disabled = 0
 and i.is_hypothetical = 0
 and ic.key_ordinal > 0

order by ic.key_ordinal

这不是我想要的.
我想要的是，列出所有用户定义的索引(这意味着没有支持唯一约束和主键的索引)以及所有列(按它们在索引定义中的出现方式排序)加上尽可能多的元数据.

Which is not exactly what I want.
What I want is, to list all user-defined indexes, (which means no indexes which support unique constraints & primary keys) with all columns (ordered by how do they appear in index definition) plus as much metadata as possible.

推荐答案

有两个sys"您可以参考的目录视图:sys.indexes 和 sys.index_columns.

There are two "sys" catalog views you can consult: sys.indexes and sys.index_columns.

这些将为您提供您可能想要的有关索引及其列的任何信息.

Those will give you just about any info you could possibly want about indices and their columns.

此查询与您要查找的内容非常接近:

This query's getting pretty close to what you're looking for:

SELECT 
     TableName = t.name,
     IndexName = ind.name,
     IndexId = ind.index_id,
     ColumnId = ic.index_column_id,
     ColumnName = col.name,
     ind.*,
     ic.*,
     col.* 
FROM 
     sys.indexes ind 
INNER JOIN 
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
INNER JOIN 
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
     sys.tables t ON ind.object_id = t.object_id 
WHERE 
     ind.is_primary_key = 0 
     AND ind.is_unique = 0 
     AND ind.is_unique_constraint = 0 
     AND t.is_ms_shipped = 0 
ORDER BY 
     t.name, ind.name, ind.index_id, ic.is_included_column, ic.key_ordinal;

相关文章