如何在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
这可不是我想要的。
我想要的是，列出所有用户定义的索引，(这意味着不支持唯一约束和主键的索引)与所有列(按它们在索引定义中的出现方式排序)以及尽可能多的元数据。
首先，请注意，以上所有查询都可能遗漏或错误地合并索引的INCLUDE列。在某些情况下，还缺少列的正确排序和/或ASC/DESC选项。
由jona修改了上述查询。顺便说一句，在我使用的许多数据库中，我都安装了自己的CLR CONCATENATE聚合函数，因此下面的代码依赖于存在这样的东西。上面的SQL语句简化为更易于维护:
SELECT
s.[name] AS [schema_name]
, t.[name] AS [table_name]
, i.[name] AS [index_name]
, dbo.Concatenate(CASE WHEN ic.[key_ordinal] > 0 AND ic.[is_descending_key] = 1 THEN c.[name] + ' DESC' WHEN key_ordinal > 0 THEN c.[name] ELSE NULL END,',',1) AS [columns]
, dbo.Concatenate(CASE WHEN ic.[is_included_column] = 1 THEN c.[name] ELSE NULL END,',',1) AS [includes]
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] 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
GROUP BY
s.[name]
, t.[name]
, i.[name]
ORDER BY
s.[name]
, t.[name]
, i.[name]
如果您的环境允许将基于clr的函数添加到其中，那么就会有许多级联聚合。
有两个“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 INX.[name] AS [Index Name]
,TBL.[name] AS [Table Name]
,DS1.[IndexColumnsNames]
,DS2.[IncludedColumnsNames]
FROM [sys].[indexes] INX
INNER JOIN [sys].[tables] TBL
ON INX.[object_id] = TBL.[object_id]
CROSS APPLY
(
SELECT STUFF
(
(
SELECT ' [' + CLS.[name] + ']'
FROM [sys].[index_columns] INXCLS
INNER JOIN [sys].[columns] CLS
ON INXCLS.[object_id] = CLS.[object_id]
AND INXCLS.[column_id] = CLS.[column_id]
WHERE INX.[object_id] = INXCLS.[object_id]
AND INX.[index_id] = INXCLS.[index_id]
AND INXCLS.[is_included_column] = 0
FOR XML PATH('')
)
,1
,1
,''
)
) DS1 ([IndexColumnsNames])
CROSS APPLY
(
SELECT STUFF
(
(
SELECT ' [' + CLS.[name] + ']'
FROM [sys].[index_columns] INXCLS
INNER JOIN [sys].[columns] CLS
ON INXCLS.[object_id] = CLS.[object_id]
AND INXCLS.[column_id] = CLS.[column_id]
WHERE INX.[object_id] = INXCLS.[object_id]
AND INX.[index_id] = INXCLS.[index_id]
AND INXCLS.[is_included_column] = 1
FOR XML PATH('')
)
,1
,1
,''
)
) DS2 ([IncludedColumnsNames])
我想到了这个，它给了我我需要的准确的概述。有帮助的是，每个索引得到一行，索引列被聚合到其中。
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
使用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
