所有索引和列表SQL Server DB中的索引列 [英] List of all index & index columns in SQL Server DB

问题描述

如何获取所有索引和列表的列表？ 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目录视图：

There are two "sys" catalog views you can consult:

select * from sys.indexes

select * from 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.index_column_id;

这篇关于所有索引和列表SQL Server DB中的索引列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！