当前位置: 首页 >>数据库 >>MSSQL >>mssql sqlserver 使用sql脚本查询所有索引,并获取索引包含的列

mssql sqlserver 使用sql脚本查询所有索引,并获取索引包含的列

时间:2020/8/28 10:14:00 【admin】

mssql sqlserver 使用sql脚本查询所有索引,并获取索引包含的列,下文的脚本适用于sqlserver日常检查,此sql脚本主要是获取sql所有索引信息及索引所对应的列…

下文讲述使用sql脚本的方法,获取所有索引信息及索引所包含的列信息,实验环境:sql server 2008 R2


下文的脚本适用于sqlserver日常检查,此sql脚本主要是获取sql所有索引信息及索引所对应的列,

例:

    SELECT

QUOTENAME(SCHEMA_NAME(t.schema_id)) AS [架构名称],

QUOTENAME(t.name) AS [数据表名称],

QUOTENAME(i.name) AS [索引名称],

i.type_desc as [索引类型],

i.is_primary_key as [是否主键],

i.is_unique as [是否唯一],

i.is_unique_constraint as [是否外键],

STUFF(REPLACE(REPLACE((

SELECT QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END AS [data()] 

FROM sys.index_columns AS ic

INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id

WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0

ORDER BY ic.key_ordinal

FOR XML PATH

), '<row>', ', '), '</row>', ''), 1, 2, '') AS [索引键列表],

STUFF(REPLACE(REPLACE((

SELECT QUOTENAME(c.name) AS [data()]

FROM sys.index_columns AS ic

INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id

WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1

ORDER BY ic.index_column_id

FOR XML PATH

), '<row>', ', '), '</row>', ''), 1, 2, '') AS [包含列信息],

u.user_seeks,

u.user_scans,

u.user_lookups,

u.user_updates

FROM sys.tables AS t

INNER JOIN sys.indexes AS i ON t.object_id = i.object_id

LEFT JOIN sys.dm_db_index_usage_stats AS u ON i.object_id = u.object_id AND i.index_id = u.index_id

WHERE t.is_ms_shipped = 0

AND i.type <> 0


扩充阅读

CopyRight:2007-2018 语言吧 备案ICP:湘ICP备09009000号-15 http://www.yuyanba.com

分享按钮