如何在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
这可不是我想要的。
我想要的是，列出所有用户定义的索引，(这意味着不支持唯一约束和主键的索引)与所有列(按它们在索引定义中的出现方式排序)以及尽可能多的元数据。
根据公认的答案和另外两个问题1,2，我整理了以下问题:
SELECT
QUOTENAME(t.name) AS TableName,
QUOTENAME(i.name) AS IndexName,
i.is_primary_key,
i.is_unique,
i.is_unique_constraint,
STUFF(REPLACE(REPLACE((
SELECT QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END AS [data()]
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.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
), '<row>', ', '), '</row>', ''), 1, 2, '') AS KeyColumns,
STUFF(REPLACE(REPLACE((
SELECT QUOTENAME(c.name) AS [data()]
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.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.index_column_id
FOR XML PATH
), '<row>', ', '), '</row>', ''), 1, 2, '') AS IncludedColumns,
u.user_seeks,
u.user_scans,
u.user_lookups,
u.user_updates
FROM sys.tables AS t
INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS u ON i.object_id = u.object_id AND i.index_id = u.index_id
WHERE t.is_ms_shipped = 0
AND i.type <> 0
该查询返回如下所示的结果，其中显示了索引的列表、它们的列和用法。非常有助于确定哪个指数比其他指数表现更好:
首先，请注意，以上所有查询都可能遗漏或错误地合并索引的INCLUDE列。在某些情况下，还缺少列的正确排序和/或ASC/DESC选项。
由jona修改了上述查询。顺便说一句，在我使用的许多数据库中，我都安装了自己的CLR CONCATENATE聚合函数，因此下面的代码依赖于存在这样的东西。上面的SQL语句简化为更易于维护:
SELECT
s.[name] AS [schema_name]
, t.[name] AS [table_name]
, i.[name] AS [index_name]
, dbo.Concatenate(CASE WHEN ic.[key_ordinal] > 0 AND ic.[is_descending_key] = 1 THEN c.[name] + ' DESC' WHEN key_ordinal > 0 THEN c.[name] ELSE NULL END,',',1) AS [columns]
, dbo.Concatenate(CASE WHEN ic.[is_included_column] = 1 THEN c.[name] ELSE NULL END,',',1) AS [includes]
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] 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
GROUP BY
s.[name]
, t.[name]
, i.[name]
ORDER BY
s.[name]
, t.[name]
, i.[name]
如果您的环境允许将基于clr的函数添加到其中，那么就会有许多级联聚合。
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，列和表名为非唯一的，没有主键
我更新了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
这是我的，工作在一个默认模式，但它可以很容易地改进
它提供了3列SQLQueries -创建/删除/重建(没有重组)
查询:
SELECT
'CREATE ' +
CASE WHEN is_primary_key=1 THEN 'CLUSTERED'
WHEN is_primary_key=0 and is_unique_constraint=0 THEN 'NONCLUSTERED'
WHEN is_primary_key=0 and is_unique_constraint=1 THEN 'UNIQUE' END
+ ' INDEX ' +
QUOTENAME(i.name) + ' ON ' +
QUOTENAME(t.name) + ' ( ' +
STUFF(REPLACE(REPLACE((
SELECT QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END AS [data()]
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.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
), '<row>', ', '), '</row>', ''), 1, 2, '') + ' ) ' -- keycols
+ COALESCE(' INCLUDE ( ' +
STUFF(REPLACE(REPLACE((
SELECT QUOTENAME(c.name) AS [data()]
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.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.index_column_id
FOR XML PATH
), '<row>', ', '), '</row>', ''), 1, 2, '') + ' ) ', -- included cols
'') as [Create],
'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(t.name) as [Drop],
'ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' +QUOTENAME(t.name) + ' REBUILD ' as [Rebuild]
FROM sys.tables AS t
INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS u ON i.object_id = u.object_id AND i.index_id = u.index_id
WHERE t.is_ms_shipped = 0
AND i.type <> 0
order by QUOTENAME(t.name), is_primary_key desc
输出
Create Drop Rebuild
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE CLUSTERED INDEX [PK_Table1] ON [Table1] ( [Tab1_ID] ) DROP INDEX [PK_Table1] ON [Table1] ALTER INDEX [PK_Table1] ON [Table1] REBUILD
CREATE UNIQUE INDEX [IX_Table1_Name] ON [Table1] ( [Tab1_Name] ) DROP INDEX [IX_Table1_Name] ON [Table1] ALTER INDEX [IX_Table1_Name] ON [Table1] REBUILD
CREATE NONCLUSTERED INDEX [IX_Table2] ON [Table2] ( [Tab2_Name], [Tab2_City] ) INCLUDE ( [Tab2_PhoneNo] ) DROP INDEX [IX_Table2] ON [Table2] ALTER INDEX [IX_Table2] ON [Table2] REBUILD
根据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]
