如何在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 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
有两个“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;
这是可行的:
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
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
我没有经过，但是我在原作者发布的查询中得到了我想要的东西。
我使用它(没有条件/过滤器)来满足我的需求，但它给出了不正确的结果
主要问题是在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
我想到了这个，它给了我我需要的准确的概述。有帮助的是，每个索引得到一行，索引列被聚合到其中。
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
