如何在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 2014工作解决方案。我在这里只包含了少量的输出字段，但您可以随意添加任何您喜欢的字段。
SELECT
o.object_id AS objectId
,o.name AS objectName
,i.index_id AS indexId
,i.name AS indexName
,i.type_desc AS typeDesc
,ic.index_column_id AS indexColumnId
,ic.key_ordinal AS keyOrdinal
,ic.is_included_column AS isIncludedColumn
,ic.column_id AS columnId
,c.name AS columnName
FROM {database}.sys.objects AS o
INNER JOIN {database}.sys.columns AS c ON
c.object_id = o.object_id
AND o.type = 'U'
INNER JOIN {database}.sys.indexes AS i ON
i.object_id = o.object_id
INNER JOIN {database}.sys.index_columns AS ic ON
ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.column_id = c.column_id
ORDER BY
o.object_id
,i.index_id
,ic.index_column_id
根据Tim Ford的代码，这是正确答案:
select tab.[name] as [table_name],
idx.[name] as [index_name],
allc.[name] as [column_name],
idx.[type_desc],
idx.[is_unique],
idx.[data_space_id],
idx.[ignore_dup_key],
idx.[is_primary_key],
idx.[is_unique_constraint],
idx.[fill_factor],
idx.[is_padded],
idx.[is_disabled],
idx.[is_hypothetical],
idx.[allow_row_locks],
idx.[allow_page_locks],
idxc.[is_descending_key],
idxc.[is_included_column],
idxc.[index_column_id]
from sys.[tables] as tab
inner join sys.[indexes] idx on tab.[object_id] = idx.[object_id]
inner join sys.[index_columns] idxc on idx.[object_id] = idxc.[object_id] and idx.[index_id] = idxc.[index_id]
inner join sys.[all_columns] allc on tab.[object_id] = allc.[object_id] and idxc.[column_id] = allc.[column_id]
where tab.[name] Like '%table_name%'
and idx.[name] Like '%index_name%'
order by tab.[name], idx.[index_id], idxc.[index_column_id]
SQL Server 2014工作解决方案。我在这里只包含了少量的输出字段，但您可以随意添加任何您喜欢的字段。
SELECT
o.object_id AS objectId
,o.name AS objectName
,i.index_id AS indexId
,i.name AS indexName
,i.type_desc AS typeDesc
,ic.index_column_id AS indexColumnId
,ic.key_ordinal AS keyOrdinal
,ic.is_included_column AS isIncludedColumn
,ic.column_id AS columnId
,c.name AS columnName
FROM {database}.sys.objects AS o
INNER JOIN {database}.sys.columns AS c ON
c.object_id = o.object_id
AND o.type = 'U'
INNER JOIN {database}.sys.indexes AS i ON
i.object_id = o.object_id
INNER JOIN {database}.sys.index_columns AS ic ON
ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.column_id = c.column_id
ORDER BY
o.object_id
,i.index_id
,ic.index_column_id
以下是最好的方法:
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引用，因为您可能不需要它。
欢呼。
我没有经过，但是我在原作者发布的查询中得到了我想要的东西。
我使用它(没有条件/过滤器)来满足我的需求，但它给出了不正确的结果
主要问题是在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
