如何在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
这可不是我想要的。
我想要的是，列出所有用户定义的索引，(这意味着不支持唯一约束和主键的索引)与所有列(按它们在索引定义中的出现方式排序)以及尽可能多的元数据。
这是一种回退到索引的方法。您可以使用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
以下是最好的方法:
SELECT sys.tables.object_id, sys.tables.name as table_name, sys.columns.name as column_name, sys.indexes.name as index_name,
sys.indexes.is_unique, sys.indexes.is_primary_key
FROM sys.tables, sys.indexes, sys.index_columns, sys.columns
WHERE (sys.tables.object_id = sys.indexes.object_id AND sys.tables.object_id = sys.index_columns.object_id AND sys.tables.object_id = sys.columns.object_id
AND sys.indexes.index_id = sys.index_columns.index_id AND sys.index_columns.column_id = sys.columns.column_id)
AND sys.tables.name = 'your_table_name'
我更喜欢使用隐式连接，因为它对我来说更容易理解。您可以删除object_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
当我有这个需求时，我使用了以下查询…
SELECT
TableName = t.name,
ColumnId = col.column_id,
ColumnName = col.name,
DataType = ty.name,
MaxSize = ty.max_length,
IsNullable = CASE WHEN (col.is_nullable = 1) THEN 'Y' END,
IsIdentity = CASE WHEN (col.is_identity = 1) THEN 'Y' END,
IsPrimaryKey = CASE WHEN (ic.column_id = col.column_id) THEN 'Y' END,
IsForeignKey = CASE WHEN (fkc.parent_column_id = col.column_id) THEN 'Y' END,
IsDefault = CASE WHEN (dc.parent_column_id = col.column_id) THEN 'Y' END
FROM
sys.tables t
INNER JOIN
sys.columns col ON t.object_id = col.object_id
LEFT JOIN
sys.indexes ind ON t.object_id = ind.object_id
LEFT JOIN
sys.index_columns ic ON ic.index_id=ind.index_id AND ic.object_id = col.object_id and ic.column_id = col.column_id
LEFT JOIN sys.foreign_key_columns fkc
ON fkc.parent_object_id = col.object_id AND fkc.parent_column_id=col.column_id
LEFT JOIN sys.default_constraints dc
ON dc.parent_object_id = col.object_id AND dc.parent_column_id=col.column_id
LEFT JOIN
sys.types ty on ty.user_type_id = col.user_type_id
WHERE
--t.name='<TABLENAME>'
t.schema_id = 10 --SCHEMA ID
AND ind.is_primary_key=1
ORDER BY
t.name, ColumnId
使用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
对于每个索引的唯一列:
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
