sql - 检查列上的索引

Question

无论如何我们可以检查表中的特定列是否有索引？我正在使用 SQL Server Management Studio 2016。我确实在网上搜索过，但所有解决方案都在谈论在整个表而不是一列上查找索引。

标签： sqlsql-serverssms

Answer 1

是的，您可以通过探索系统视图sys.indexes和sys.index_columns来做到这一点。

例如，如果您想了解 AdventureWorks 数据库中表 'Production.BillOfMaterials' 中的 'StartDate' 列是否有索引，

您可以执行以下查询。

DECLARE @colName AS SYSNAME = 'StartDate'
DECLARE @tableName AS NVARCHAR(256) = 'Production.BillOfMaterials'
    
SELECT i.name AS index_name, 
           COL_NAME(ic.object_id, ic.column_id) AS column_name, 
           ic.index_column_id, 
           ic.key_ordinal, 
           ic.is_included_column
    FROM sys.indexes AS i
         INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id
                                               AND i.index_id = ic.index_id
    WHERE i.object_id = OBJECT_ID(@tableName)
          AND COL_NAME(ic.object_id, ic.column_id) = @colName;

…你会得到如下结果

根据您的评论，我创建了另外两个示例

首先——根据列名过滤

在下面的代码段中有一个答案，为什么你不能得到想要的结果。Object_Id 是基于模式的，因此只传递没有模式的表名会产生 NULL 值。

在示例中，我正在根据列“StartDate”过滤 AdvantureWorks2016 数据库中的系统视图

DECLARE @colName AS SYSNAME = 'StartDate'
--DECLARE @tableName AS NVARCHAR(256) = 'Production.BillOfMaterials'


SELECT i.name [Index Name], 
       OBJECT_SCHEMA_NAME(ic.object_id) + '.' + OBJECT_NAME(ic.object_id) [Table Name],
       COL_NAME(ic.object_id, ic.column_id) [Column Name], 
       ic.index_column_id, 
       ic.key_ordinal, 
       ic.is_included_column
FROM sys.indexes AS i
     INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id
                                           AND i.index_id = ic.index_id
WHERE COL_NAME(ic.object_id, ic.column_id) = @colName
ORDER BY [Table Name]

---

结果如下图所示。

基于表列表的二次过滤

如果您想缩小表格的范围。假设您知道要检查的表的列表，您可以使用以下代码段。

DECLARE @tables AS TABLE 
(
TableName NVARCHAR(256)
)
INSERT INTO @tables
VALUES 
('HumanResources.EmployeeDepartmentHistory'),
('Production.BillOfMaterials'),
('Production.ProductCostHistory'),
('Production.ProductListPriceHistory')

SELECT i.name [Index Name], 
       OBJECT_SCHEMA_NAME(ic.object_id) + '.' + OBJECT_NAME(ic.object_id) [Table Name],
       COL_NAME(ic.object_id, ic.column_id) [Column Name], 
       ic.index_column_id, 
       ic.key_ordinal, 
       ic.is_included_column
FROM sys.indexes AS i
     INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id
                                           AND i.index_id = ic.index_id

WHERE EXISTS 
(
SELECT * FROM @tables t WHERE t.TableName = OBJECT_SCHEMA_NAME(ic.object_id) + '.' + OBJECT_NAME(ic.object_id)
)
ORDER BY [Table Name]

结果如下图所示。