如何在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
这可不是我想要的。
我想要的是，列出所有用户定义的索引，(这意味着不支持唯一约束和主键的索引)与所有列(按它们在索引定义中的出现方式排序)以及尽可能多的元数据。
sELECT
TableName = t.name,
IndexName = ind.name,
--IndexId = ind.index_id,
ColumnId = ic.index_column_id,
ColumnName = col.name,
key_ordinal,
ind.type_desc
--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
and t.name='CompanyReconciliation' --table name
and key_ordinal>0
ORDER BY
t.name, ind.name, ind.index_id, ic.index_column_id
这是可行的:
DECLARE @IndexInfo TABLE (index_name varchar(250)
,index_description varchar(250)
,index_keys varchar(250)
)
INSERT INTO @IndexInfo
exec sp_msforeachtable 'sp_helpindex ''?'''
select * from @IndexInfo
这不会返回表名，你会得到所有没有索引的表的警告，如果这是一个问题，你可以在有索引的表上创建一个循环，像这样:
DECLARE @IndexInfoTemp TABLE (index_name varchar(250)
,index_description varchar(250)
,index_keys varchar(250)
)
DECLARE @IndexInfo TABLE (table_name sysname
,index_name varchar(250)
,index_description varchar(250)
,index_keys varchar(250)
)
DECLARE @Tables Table (RowID int not null identity(1,1)
,TableName sysname
)
DECLARE @MaxRow int
DECLARE @CurrentRow int
DECLARE @CurrentTable sysname
INSERT INTO @Tables
SELECT
DISTINCT t.name
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.Name IS NOT NULL
SELECT @MaxRow=@@ROWCOUNT,@CurrentRow=1
WHILE @CurrentRow<=@MaxRow
BEGIN
SELECT @CurrentTable=TableName FROM @Tables WHERE RowID=@CurrentRow
INSERT INTO @IndexInfoTemp
exec sp_helpindex @CurrentTable
INSERT INTO @IndexInfo
(table_name , index_name , index_description , index_keys)
SELECT
@CurrentTable , index_name , index_description , index_keys
FROM @IndexInfoTemp
DELETE FROM @IndexInfoTemp
SET @CurrentRow=@CurrentRow+1
END --WHILE
SELECT * from @IndexInfo
编辑
如果你愿意，你可以过滤数据，这里有一些例子(这两种方法都适用):
SELECT * FROM @IndexInfo WHERE index_description NOT LIKE '%primary key%'
SELECT * FROM @IndexInfo WHERE index_description NOT LIKE '%nonclustered%' AND index_description LIKE '%clustered%'
SELECT * FROM @IndexInfo WHERE index_description LIKE '%unique%'
select i.object_id, i.name as [index] , STRING_AGG(c.name,', ') as [column], o.name as [table] from sys.indexes i
INNER join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id
INNER join sys.columns c on c.object_id = ic.object_id and ic.column_id = c.column_id
INNER JOIN sys.objects o on o.object_id = i.object_id
where i.object_id > 100 and i.is_primary_key = 0 and i.is_unique = 0 and o.is_ms_shipped <> 1
group by i.object_id, i.name, o.name
order by i.name
将此用于sql 2016及更高级别，它会显示object_id, indexname，列和表名为非唯一的，没有主键
我需要得到特定的索引，它们的索引列和包含的列。以下是我使用的查询:
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])
