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

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

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


当前回答

对于每个索引的唯一列:

select s.name, t.name, i.name, i.index_id,c.name,c.column_id
 from sys.schemas s
inner join sys.tables t 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
    and ic.index_id=i.index_id
inner join sys.columns c on c.object_id = t.object_id 
    and ic.column_id = c.column_id
where i.object_id = object_id('previous.account_1')  
order by index_id,column_id

其他回答

这是一种回退到索引的方法。您可以使用SHOWCONTIG来评估碎片。它将列出数据库或表的所有索引,以及统计信息。我要提醒的是,在大型数据库上,它可能是长时间运行的。对我来说,这种方法的好处之一是您不必是管理员就可以使用它。

——显示数据库中所有索引的碎片信息

SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG WITH ALL_INDEXES
GO

...完成后关闭NOCOUNT

——显示表中所有索引的碎片信息

SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG (authors) WITH ALL_INDEXES
GO

——显示特定索引上的碎片信息

SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG (authors,aunmind)
GO

有两个“sys”目录视图可以参考:Indexes和sys.index_columns。

这些会给你关于下标和它们的列的任何信息。

编辑:这个查询非常接近你所寻找的:

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;
select i.object_id, i.name as [index] , STRING_AGG(c.name,', ') as [column], o.name as [table] from sys.indexes i
INNER join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id
INNER join sys.columns c on c.object_id = ic.object_id and ic.column_id = c.column_id
INNER JOIN sys.objects o on o.object_id = i.object_id
where i.object_id > 100 and i.is_primary_key = 0 and i.is_unique = 0 and o.is_ms_shipped <> 1
group by i.object_id, i.name, o.name
order by i.name

将此用于sql 2016及更高级别,它会显示object_id, indexname,列和表名为非唯一的,没有主键

使用SQL Server 2016,这给出了所有索引的完整列表,并包含每个表的转储,以便您可以查看表之间的关系。它还显示包含在覆盖索引中的列:

select t.name TableName, i.name IdxName, c.name ColName
    , ic.index_column_id ColPosition
    , i.type_desc Type
    , case when i.is_primary_key = 1 then 'Yes' else '' end [Primary?]
    , case when i.is_unique = 1 then 'Yes' else '' end [Unique?]
    , case when ic.is_included_column = 0 then '' else 'Yes - Included' end [CoveredColumn?]
    , 'indexes >>>>' [*indexes*], i.*, 'index_columns >>>>' [*index_columns*]
    , ic.*, 'tables >>>>' [*tables*]
    , t.*, 'columns >>>>' [*columns*], c.*
from sys.index_columns ic
join sys.tables t on t.object_id = ic.object_id
join sys.columns c on c.object_id = t.object_id and c.column_id = ic.column_id
join sys.indexes i on i.object_id = t.object_id and i.index_id = ic.index_id
order by TableName, IdxName, ColPosition

我没有经过,但是我在原作者发布的查询中得到了我想要的东西。

我使用它(没有条件/过滤器)来满足我的需求,但它给出了不正确的结果

主要问题是在index_id上没有连接条件的情况下得到叉乘

SELECT S.NAME SCHEMA_NAME,T.NAME TABLE_NAME,I.NAME INDEX_NAME,C.NAME COLUMN_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.INDEX_ID    = I.INDEX_ID**
   AND IC.COLUMN_ID = C.COLUMN_ID
 WHERE 1=1

ORDER BY I.NAME,I.INDEX_ID,IC.KEY_ORDINAL