如何在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
这可不是我想要的。
我想要的是，列出所有用户定义的索引，(这意味着不支持唯一约束和主键的索引)与所有列(按它们在索引定义中的出现方式排序)以及尽可能多的元数据。
我更新了KFD9的答案。
我调整了他们的版本，以支持include-specification，而不使用已弃用的indexkey_property
这为索引和约束提供了create和drop语句。
with indexes as (
SELECT
schema_name(schema_id) as SchemaName, OBJECT_NAME(si.object_id) as TableName, si.name as IndexName,
(CASE is_primary_key WHEN 1 THEN 'PK' ELSE '' END) as PK,
(CASE is_unique WHEN 1 THEN '1' ELSE '0' END)+' '+
(CASE si.type WHEN 1 THEN 'C' WHEN 3 THEN 'X' ELSE 'B' END)+' ' as 'Type', -- B=basic, C=Clustered, X=XML
(select string_agg(CAST('[' + c.name + ']' + case when is_descending_key = 1 then ' DESC' else '' end AS NVARCHAR(MAX)), ',') within group (order by index_column_id)
from sys.index_columns ic JOIN sys.columns c on ic.column_id = c.column_id and ic.object_id = c.object_id where ic.index_id = si.index_id and ic.object_id = si.object_id and ic.is_included_column = 0) Cols,
(select string_agg(CAST('[' + c.name + ']' + case when is_descending_key = 1 then ' DESC' else '' end AS NVARCHAR(MAX)), ',') within group (order by index_column_id)
from sys.index_columns ic JOIN sys.columns c on ic.column_id = c.column_id and ic.object_id = c.object_id where ic.index_id = si.index_id and ic.object_id = si.object_id and ic.is_included_column = 1) IncludedCols,
(select count(*) from sys.index_columns ic where ic.index_id = si.index_id and ic.object_id = si.object_id) IndexColsCount
FROM sys.indexes as si
LEFT JOIN sys.objects as so on so.object_id=si.object_id
WHERE index_id>0 -- omit the default heap
and OBJECTPROPERTY(si.object_id,'IsMsShipped')=0 -- omit system tables
and not (schema_name(schema_id)='dbo' and OBJECT_NAME(si.object_id)='sysdiagrams') -- omit sysdiagrams
)
SELECT SchemaName, TableName, IndexName,
(CASE pk
WHEN 'PK' THEN 'ALTER '+
'TABLE ['+SchemaName+'].['+TableName+'] ADD CONSTRAINT ['+IndexName+'] PRIMARY KEY'+
(CASE substring(Type,3,1) WHEN 'C' THEN ' CLUSTERED' ELSE '' END)
ELSE 'CREATE '+
(CASE substring(Type,1,1) WHEN '1' THEN 'UNIQUE ' ELSE '' END)+
(CASE substring(Type,3,1) WHEN 'C' THEN 'CLUSTERED ' ELSE '' END)+
'INDEX ['+IndexName+'] ON ['+SchemaName+'].['+TableName+']'
END)+
' ('+Cols+')'+
isnull(' include ('+IncludedCols+')', '')+
'' as CreateIndex,
CASE pk
WHEN 'PK' THEN 'ALTER '+
'TABLE ['+SchemaName+'].['+TableName+'] DROP CONSTRAINT ['+IndexName+'] '
ELSE 'DROP INDEX ['+IndexName+'] ON ['+SchemaName+'].['+TableName + ']'
END AS DropIndex,
IndexColsCount
FROM indexes
ORDER BY SchemaName,TableName,IndexName
这是可行的:
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%'
我可以大胆回答这个饱和的问题吗?
这是@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;
对于每个索引的唯一列:
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
