如何在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
这可不是我想要的。
我想要的是，列出所有用户定义的索引，(这意味着不支持唯一约束和主键的索引)与所有列(按它们在索引定义中的出现方式排序)以及尽可能多的元数据。
使用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
这是一种回退到索引的方法。您可以使用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
with connect(schema_name,table_name,index_name,index_column_id,column_name) as
( select s.name schema_name, t.name table_name, i.name index_name, index_column_id, cast(c.name as varchar(max)) 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 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 index_column_id=1
union all
select s.name schema_name, t.name table_name, i.name index_name, ic.index_column_id, cast(connect.column_name + ',' + c.name as varchar(max)) 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 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 join connect on
connect.index_column_id+1 = ic.index_column_id
and connect.schema_name = s.name
and connect.table_name = t.name
and connect.index_name = i.name)
select connect.schema_name,connect.table_name,connect.index_name,connect.column_name
from connect join (select schema_name,table_name,index_name,MAX(index_column_id) index_column_id
from connect group by schema_name,table_name,index_name) mx
on connect.schema_name = mx.schema_name
and connect.table_name = mx.table_name
and connect.index_name = mx.index_name
and connect.index_column_id = mx.index_column_id
order by 1,2,3
我可以大胆回答这个饱和的问题吗?
这是@marc_s答案的自由重做，混合了来自@Tim Ford的一些东西，目标是有一个更干净和更简单的结果集和最终显示和排序，以满足我当前的需要。
SELECT
OBJECT_SCHEMA_NAME(t.[object_id],DB_ID()) AS [Schema],
t.[name] AS [TableName],
ind.[name] AS [IndexName],
col.[name] AS [ColumnName],
ic.column_id AS [ColumnId],
ind.[type_desc] AS [IndexTypeDesc],
col.is_identity AS [IsIdentity],
ind.[is_unique] AS [IsUnique],
ind.[is_primary_key] AS [IsPrimaryKey],
ic.[is_descending_key] AS [IsDescendingKey],
ic.[is_included_column] AS [IsIncludedColumn]
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
t.is_ms_shipped = 0
--ind.is_primary_key = 1 -- include or not pks, etc
--AND ind.is_unique = 0
--AND ind.is_unique_constraint = 0
ORDER BY
[Schema],
TableName,
IndexName,
[ColumnId],
ColumnName
有两个“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;
