在sqlserver中,如何查询数据库中有哪些索引,本文会说到几种方式。
一:
SELECT 索引名称 = a.name ,
表名 = c.name ,
索引字段名 = d.name ,
索引字段位置 = d.colid
FROM sysindexes a
JOIN sysindexkeys b ON a.id = b.id
AND a.indid = b.indid
JOIN sysobjects c ON b.id = c.id
JOIN syscolumns d ON b.id = d.id
AND b.colid = d.colid
WHERE a.indid NOT IN ( 0, 255 )
--and c.xtype='U' and c.status>0 --查所有用户表
--AND c.name='message' --查指定表
ORDER BY c.name ,
a.name ,
d.name;
结果示例:
二:
exec sp_helpindex 表名
结果示例:
三:
WITH info
AS ( SELECT TableId = O.[object_id] ,
TableName = O.name ,
IndexId = ISNULL(KC.[object_id], IDX.index_id) ,
IndexName = IDX.name ,
IndexType = ISNULL(KC.type_desc, 'Index') ,
Index_Column_id = IDXC.index_column_id ,
ColumnID = C.column_id ,
ColumnName = C.name ,
Sort = CASE INDEXKEY_PROPERTY(IDXC.[object_id],
IDXC.index_id,
IDXC.index_column_id,
'IsDescending')
WHEN 1 THEN 'DESC'
WHEN 0 THEN 'ASC'
ELSE ''
END ,
PrimaryKey = CASE WHEN IDX.is_primary_key = 1
THEN N'√'
ELSE N''
END ,
[UQIQUE] = CASE WHEN IDX.is_unique = 1 THEN N'√'
ELSE N''
END ,
Ignore_dup_key = CASE WHEN IDX.ignore_dup_key = 1
THEN N'√'
ELSE N''
END ,
Disabled = CASE WHEN IDX.is_disabled = 1 THEN N'√'
ELSE N''
END ,
Fill_factor = IDX.fill_factor ,
Padded = CASE WHEN IDX.is_padded = 1 THEN N'√'
ELSE N''
END
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC ON IDX.[object_id] = IDXC.[object_id]
AND IDX.index_id = IDXC.index_id
LEFT JOIN sys.key_constraints KC ON IDX.[object_id] = KC.[parent_object_id]
AND IDX.index_id = KC.unique_index_id
INNER JOIN sys.objects O ON O.[object_id] = IDX.[object_id]
INNER JOIN sys.columns C ON O.[object_id] = C.[object_id]
AND O.type = 'U'
AND O.is_ms_shipped = 0
AND IDXC.column_id = C.column_id
)
SELECT *
FROM info a;
结果示例:
四:
WITH info
AS ( SELECT CASE WHEN t.[type] = 'U' THEN '表'
WHEN t.[type] = 'V' THEN '视图'
END AS '类型' ,
SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS '(表/视图)名称' ,
i.[name] AS 索引名称 ,
SUBSTRING(column_names, 1, LEN(column_names) - 1) AS '列名' ,
CASE WHEN i.[type] = 1 THEN '聚集索引'
WHEN i.[type] = 2 THEN '非聚集索引'
WHEN i.[type] = 3 THEN 'XML索引'
WHEN i.[type] = 4 THEN '空间索引'
WHEN i.[type] = 5 THEN '聚簇列存储索引'
WHEN i.[type] = 6 THEN '非聚集列存储索引'
WHEN i.[type] = 7 THEN '非聚集哈希索引'
END AS '索引类型' ,
CASE WHEN i.is_unique = 1 THEN '唯一'
ELSE '不唯一'
END AS '索引是否唯一'
FROM sys.objects t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
CROSS APPLY ( SELECT col.[name] + ', '
FROM sys.index_columns ic
INNER JOIN sys.columns col ON ic.object_id = col.object_id
AND ic.column_id = col.column_id
WHERE ic.object_id = t.object_id
AND ic.index_id = i.index_id
ORDER BY col.column_id
FOR
XML PATH('')
) D ( column_names )
WHERE t.is_ms_shipped <> 1
AND index_id > 0
)
SELECT *
FROM info a;
结果示例:
ending~