详解sqlserver查询表索引

1. 概述

在SQL Server中，索引是提高查询性能的常用方法之一。通过创建索引，可以使查询更快速、更有效率。本文将详细介绍如何查询表的索引。

2. 查询表索引的基本语法

查询表索引的SQL语句如下：

SELECT  
    t.name as TableName,  
    i.name as IndexName,  
    i.type_desc as IndexType,  
    ic.index_column_id as ColumnOrder,  
    col.name as ColumnName,  
    ic.is_descending_key as IsDescending,  
    ic.is_included_column as IsIncluded  
FROM sys.indexes i  
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.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 i.object_id = t.object_id  
WHERE i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND i.is_unique = 0 AND i.is_hypothetical = 0 AND t.is_ms_shipped = 0  
ORDER BY t.name, i.name, ic.index_column_id

说明：

t.name：表名

i.name：索引名

i.type_desc：索引类型

ic.index_column_id：索引列序号

col.name：索引列名

ic.is_descending_key：是否为降序排列

ic.is_included_column：是否为包含列

3. 查询表所有索引的语句

查询表的所有索引的SQL语句如下：

SELECT  
    i.name as IndexName  
FROM sys.indexes i  
INNER JOIN sys.tables t ON i.object_id = t.object_id  
WHERE i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND i.is_unique = 0 AND i.is_hypothetical = 0 AND t.is_ms_shipped = 0
ORDER BY i.name

说明：该语句仅返回表的索引名称。

4. 查询特定索引的语句

查询特定索引的SQL语句如下：

SELECT  
    t.name as TableName,  
    i.name as IndexName,  
    i.type_desc as IndexType,  
    ic.index_column_id as ColumnOrder,  
    col.name as ColumnName,  
    ic.is_descending_key as IsDescending,  
    ic.is_included_column as IsIncluded  
FROM sys.indexes i  
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.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 i.object_id = t.object_id  
WHERE i.name = 'IndexName' AND t.name = 'TableName'
ORDER BY ic.index_column_id

说明：替换IndexName和TableName为相应的索引名称和表名。

5. 查询表的主键

查询表的主键的SQL语句如下：

SELECT  
    t.name as TableName,  
    i.name as IndexName,  
    i.type_desc as IndexType,  
    ic.index_column_id as ColumnOrder,  
    col.name as ColumnName,  
    ic.is_descending_key as IsDescending,  
    ic.is_included_column as IsIncluded  
FROM sys.indexes i  
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.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 i.object_id = t.object_id  
WHERE i.is_primary_key = 1 AND t.name = 'TableName'
ORDER BY ic.index_column_id

说明：替换TableName为相应的表名。

6. 查询表的唯一约束

查询表的唯一约束的SQL语句如下：

SELECT  
    t.name as TableName,  
    i.name as IndexName,  
    i.type_desc as IndexType,  
    ic.index_column_id as ColumnOrder,  
    col.name as ColumnName,  
    ic.is_descending_key as IsDescending,  
    ic.is_included_column as IsIncluded  
FROM sys.indexes i  
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.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 i.object_id = t.object_id  
WHERE i.is_unique_constraint = 1 AND i.is_primary_key = 0 AND t.name = 'TableName'
ORDER BY ic.index_column_id

说明：替换TableName为相应的表名。

7. 查询表的聚集索引

查询表的聚集索引的SQL语句如下：

SELECT  
    t.name as TableName,  
    i.name as IndexName,  
    i.type_desc as IndexType,  
    ic.index_column_id as ColumnOrder,  
    col.name as ColumnName,  
    ic.is_descending_key as IsDescending,  
    ic.is_included_column as IsIncluded  
FROM sys.indexes i  
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.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 i.object_id = t.object_id  
WHERE i.index_id = 1 AND t.name = 'TableName'
ORDER BY ic.index_column_id

说明：替换TableName为相应的表名。

8. 查询表的非聚集索引

查询表的非聚集索引的SQL语句如下：

SELECT  
    t.name as TableName,  
    i.name as IndexName,  
    i.type_desc as IndexType,  
    ic.index_column_id as ColumnOrder,  
    col.name as ColumnName,  
    ic.is_descending_key as IsDescending,  
    ic.is_included_column as IsIncluded  
FROM sys.indexes i  
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.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 i.object_id = t.object_id  
WHERE i.index_id > 1 AND t.name = 'TableName'
ORDER BY ic.index_column_id

说明：替换TableName为相应的表名。

9. 查询表的空间索引

查询表的空间索引的SQL语句如下：

SELECT  
    t.name as TableName,  
    i.name as IndexName,  
    i.type_desc as IndexType,  
    ic.index_column_id as ColumnOrder,  
    col.name as ColumnName,  
    ic.is_descending_key as IsDescending,  
    ic.is_included_column as IsIncluded  
FROM sys.indexes i  
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.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 i.object_id = t.object_id  
WHERE i.type = 4 AND t.name = 'TableName'
ORDER BY ic.index_column_id

说明：替换TableName为相应的表名。

总结

通过上述SQL语句，可以方便地查询表的索引。在使用索引时，需要注意索引的类型、列序号、是否为降序排列和是否为包含列等信息。