利用SQL语句查询数据库中所有索引

创建时间：2011年12月26日 09:19 阅读次数：(23060)

分享到：

本章我们就要讲解一下如何利用sql语句来查询出数据库中所有索引明细。当然了，我们可以在microsoft sql server management studio中选择"表"- >"索引"来查看单个表的索引明细。但这样，查询效率就很低了。

要利用sql查询出数据中所有索引，首先要了解目录视图sys.indexes。
sys.indexes的定义如下：
每个表格对象（例如，表、视图或表值函数）的索引或堆都包含一行。即这个视图中包含了数据库中所有的索引数据。具体列的说明如下：

列名	数据类型	说明
object_id	int	该索引所属对象的 ID。
name	sysname	索引的名称。name 只在该对象中是唯一的。 NULL = 堆
index_id	int	索引的 ID。index_id 只在该对象中是唯一的。 0 = 堆 1 = 聚集索引 > 1 = 非聚集索引
type	tinyint	索引的类型： 0 = 堆 1 = 聚集 2 = 非聚集 3 = XML
type_desc	nvarchar(60)	索引类型的说明： HEAP CLUSTERED NONCLUSTERED XML
is_unique	bit	1 = 索引是唯一的。0 = 索引不是唯一的。
data_space_id	int	该索引的数据空间的 ID。数据空间是文件组或分区方案。0 = object_id 是表值函数。
ignore_dup_key	bit	1 = IGNORE_DUP_KEY 是 ON。0 = IGNORE_DUP_KEY 是 OFF。
is_primary_key	bit	1 = 索引是 PRIMARY KEY 约束的一部分。
is_unique_constraint	bit	1 = 索引是 UNIQUE 约束的一部分。
fill_factor	tinyint	> 0 = 创建或重新生成索引时使用的 FILLFACTOR 百分比。0 = 默认值
is_padded	bit	1 = PADINDEX 是 ON。0 = PADINDEX 是 OFF。
is_disabled	bit	1 = 禁用索引。0 = 不禁用索引。
is_hypothetical	bit	1 = 索引是假设的，不能直接用作数据访问路径。假设的索引包含列级统计信息。0 = 索引不是假设的。
allow_row_locks	bit	1 = 索引允许行锁。0 = 索引不允许行锁。
allow_page_locks	bit	1 = 索引允许页锁。0 = 索引不允许页锁。

如果要查询到索引中的列信息，还需要结合目录视图sys.index_columns，这个视图中包含了所有索引中的列的信息。
具体列的说明如下：

列名	数据类型	说明
object_id	int	定义了索引的对象的 ID。
index_id	int	定义了列的索引的 ID。
index_column_id	int	索引列的 ID。index_column_id 仅在 index_id 内是唯一的。
column_id	int	object_id 中的列的 ID。 0 = 非聚集索引中的行标识符 (RID)。 column_id 仅在 object_id 中是唯一的。
key_ordinal	tinyint	键列集内的序数（从 1 开始）。 0 = 非键列，或者是 XML 索引。由于 xml 类型的列不可比较，因此 XML 索引不会导致对基础列值排序。因为 XML 索引不是键，所以 key_ordinal 值将始终是 0。
partition_ordinal	tinyint	分区列集内的序数（从 1 开始）。 0 = 非分区列。
is_descending_key	bit	1 = 索引键列采用降序排序。 0 = 索引键列采用升序排序。
is_included_column	bit	1 = 列是使用 CREATE INDEX INCLUDE 子句加入索引的非键列。 0 = 列不是包含性列。

要查看索引的数据明细，请参考以下链接：
利用sys.dm_db_index_physical_stats查看索引碎片等数据

结合这两个视图，再结合一些sql常用的系统表，我们可以写出如下sql来达到我们的目的。

select t1.name as 表名,
    t2.name as 索引名,
    t4.index_column_id as 列的序号
    t5.name as 列名,
    t6.name as 列的类型,
t6.max_length as 列的最大长度,
t6.precision as 列的精度,
t6.scale as 列的小数位数
from sys.objects t1
join sys.objects t2 on t2.parent_object_id = t1.object_id
join sys.indexes t3 on t3.object_id = t2.parent_object_id and t3.name = t2.name
join sys.index_columns t4 on t4.object_id = t3.object_id and t4.index_id = t3.index_id
join sys.columns t5 on t5.object_id = t1.object_id and t5.column_id = t4.column_id
join sys.types t6 on t5.user_type_id=t6.user_type_id

当然了，如何该列为varchar或者nvarchar等没有精度与小数位数的类型，precision与scale都等于0。

上面是查询所有索引的sql,如果只要查看所有的主键索引，加上过滤条件即可：

select t1.name as TableName,
    t2.name as PrimaryName,
    t4.index_column_id as IndexColumnID,
    t5.name as ColumnName,
    t6.name as typename,
t6.max_length,
t6.precision,
t6.scale
from sys.objects t1
join sys.objects t2 on t2.parent_object_id = t1.object_id
join sys.indexes t3 on t3.object_id = t2.parent_object_id and t3.name = t2.name
join sys.index_columns t4 on t4.object_id = t3.object_id and t4.index_id = t3.index_id
join sys.columns t5 on t5.object_id = t1.object_id and t5.column_id = t4.column_id
join sys.types t6 on t5.user_type_id=t6.user_type_id
where t2.type = 'pk'

上面是查看数据库中所有索引，如果只要查看单个表的索引，加上过滤条件即可：

本章介绍到这，希望能给大家带来帮助。

来源：.net学习网
说明：所有来源为 .net学习网的文章均为原创，如有转载，请在转载处标注本页地址，谢谢！

【编辑：Wyf】

打赏