使用MSSQL查询所有索引的方法-猿码集

使用MSSQL查询所有索引的方法

介绍

在MSSQL数据库中，索引是一种可以提高查询性能的关键组成部分。它们可以帮助数据库引擎快速查找和定位数据，从而提高查询效率。本文将介绍如何使用MSSQL查询所有索引的方法。

查询所有索引

通过查询系统表sys.indexes，可以查找到当前数据库中的所有索引。下面是查询语句：

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [TABLE_NAME], 
    A.[NAME] AS [INDEX_NAME], 
    B.[NAME] AS [COLUMN_NAME] 
FROM SYS.INDEXES A 
INNER JOIN SYS.INDEX_COLUMNS C 
    ON A.[OBJECT_ID] = C.[OBJECT_ID] AND A.INDEX_ID = C.INDEX_ID 
INNER JOIN SYS.COLUMNS B 
    ON C.[OBJECT_ID] = B.[OBJECT_ID] AND C.COLUMN_ID = B.COLUMN_ID 
WHERE A.[NAME] IS NOT NULL 
ORDER BY [TABLE_NAME], [INDEX_NAME], C.[KEY_Ordinal]

该查询语句将返回所有索引相关的信息，包括索引名称、所属表格和索引列。

根据表格查询索引

如果您只需要查询特定表格中的索引，可以添加一个WHERE子句来过滤结果。下面是查询语句：

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [TABLE_NAME], 
    A.[NAME] AS [INDEX_NAME], 
    B.[NAME] AS [COLUMN_NAME] 
FROM SYS.INDEXES A 
INNER JOIN SYS.INDEX_COLUMNS C 
    ON A.[OBJECT_ID] = C.[OBJECT_ID] AND A.INDEX_ID = C.INDEX_ID 
INNER JOIN SYS.COLUMNS B 
    ON C.[OBJECT_ID] = B.[OBJECT_ID] AND C.COLUMN_ID = B.COLUMN_ID 
WHERE OBJECT_NAME(A.[OBJECT_ID]) = 'YourTableName'
ORDER BY [TABLE_NAME], [INDEX_NAME], C.[KEY_Ordinal]

将查询结果限制为指定表格名称的索引信息。

查询唯一索引

如果您只需要查询唯一索引（索引中不允许重复值），可以添加WHERE子句来过滤结果。下面是查询语句：

SELECT OBJECT_NAME(I.OBJECT_ID) AS [TABLE_NAME], 
       I.NAME AS [INDEX_NAME], 
       COL_NAME(IC.OBJECT_ID, IC.COLUMN_ID) AS [COLUMN_NAME] 
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.IS_UNIQUE = 1

查询聚集索引和非聚集索引

MSSQL数据库中的索引可以分为两种类型：聚集索引和非聚集索引。聚集索引是主键或唯一索引，它们对应于表格的物理存储方式。非聚集索引是普通的索引，它们没有对应于表格的物理存储方式。

下面是查询聚集索引和非聚集索引的查询语句：

SELECT DISTINCT
    OBJECT_NAME(ID) AS TableName,
    name as [Index Name],
    CASE
        WHEN is_unique = 1 THEN 'Unique'
        ELSE 'Non-Unique'
    END as [Unique],
    CASE
        WHEN is_primary_key = 1 THEN 'Primary Key'
        WHEN is_unique_constraint = 1 THEN 'Unique Key'
        ELSE 'Indexed'
    END as [Index Type],
    CASE
        WHEN index_id = 1 THEN 'Clustered'
        ELSE 'Non-Clustered'
    END as [Index Order]
FROM
    sys.indexes
WHERE
    OBJECTPROPERTY(ID, 'IsMSShipped') = 0
ORDER BY
    TableName,
    [Index Order] DESC

该查询将返回聚集索引和非聚集索引的信息，包括索引名称、类型和所属表格。

查询索引大小

如果您需要查询索引的大小，可以使用以下查询语句：

SELECT
    TableName = OBJECT_NAME(a.object_id),
    IndexName = c.name,
    IndexMB = CONVERT(DECIMAL(15,2),ROUND(c.size/1024.0/1024.0,2)),
    [Count] = c.rows
FROM
    sys.partitions a
    JOIN sys.indexes b ON a.object_id = b.object_id AND a.index_id = b.index_id
    JOIN sys.allocation_units c ON a.partition_id = c.container_id
WHERE
    c.[type] IN (1,2,3)
ORDER BY
    CONVERT(DECIMAL(15,2),ROUND(c.size/1024.0/1024.0,2)) desc

该查询将返回索引名称、所属表格、索引大小和索引行数。

结论

通过查询系统表信息，您可以轻松地获得当前数据库中的所有索引，包括索引名称、所属表格和索引列。您也可以过滤特定的表格、唯一索引或索引类型，以满足您的特定需求。此外，了解索引的大小和行数也非常有用，有助于识别问题和优化性能。

使用MSSQL查询所有索引的方法