如何在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

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


当前回答

既然你的配置文件声明你使用的是。net,那么你可以通过编程的方式使用服务器管理对象(SMO)…除此之外,上面的任何答案都非常棒。

其他回答

这是一种回退到索引的方法。您可以使用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

下面给出了类似于sp_helpindex的表名

select T.name as TableName, I.name as IndexName, AC.Name as ColumnName, I.type_desc as IndexType 
from sys.tables as T inner join sys.indexes as I on T.[object_id] = I.[object_id] 
   inner join sys.index_columns as IC on IC.[object_id] = I.[object_id] and IC.[index_id] = I.[index_id] 
   inner join sys.all_columns as AC on IC.[object_id] = AC.[object_id] and IC.[column_id] = AC.[column_id] 
order by T.name, I.name

我想到了这个,它给了我我需要的准确的概述。有帮助的是,每个索引得到一行,索引列被聚合到其中。

select 
    o.name as ObjectName, 
    i.name as IndexName, 
    i.is_primary_key as [PrimaryKey],
    SUBSTRING(i.[type_desc],0,6) as IndexType,
    i.is_unique as [Unique],
    Columns.[Normal] as IndexColumns,
    Columns.[Included] as IncludedColumns
from sys.indexes i 
join sys.objects o on i.object_id = o.object_id
cross apply
(
    select
        substring
        (
            (
                select ', ' + co.[name]
                from sys.index_columns ic
                join sys.columns co on co.object_id = i.object_id and co.column_id = ic.column_id
                where ic.object_id = i.object_id and ic.index_id = i.index_id and ic.is_included_column = 0
                order by ic.key_ordinal
                for xml path('')
            )
            , 3
            , 10000
        )    as [Normal]    
        , substring
        (
            (
                select ', ' + co.[name]
                from sys.index_columns ic
                join sys.columns co on co.object_id = i.object_id and co.column_id = ic.column_id
                where ic.object_id = i.object_id and ic.index_id = i.index_id and ic.is_included_column = 1
                order by ic.key_ordinal
                for xml path('')
            )
            , 3
            , 10000
        )    as [Included]    

) Columns
where o.[type] = 'U' --USER_TABLE
order by o.[name], i.[name], i.is_primary_key desc

可以使用sp_helpindex查看一个表的所有索引。

EXEC sys.sp_helpindex @objname = N'User' -- nvarchar(77)

对于所有索引,您可以遍历sys.;对象获取每个表的所有索引。

有两个“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;