表包含全部索引的索引结构

database

DECLARE@tblnvarchar(265)

SELECT@tbl=""

SELECT o.name,i.index_id, i.name, i.type_desc,

substring(ikey.cols, 3, len(ikey.cols))AS key_cols,

substring(inc.cols, 3, len(inc.cols)) ASincluded_cols,

stats_date(o.object_id, i.index_id) ASstats_date,

i.filter_definition

FROM sys.objects o

JOIN sys.indexes i ON i.object_id= o.object_id

CROSS APPLY (SELECT", "+ c.name +

CASE ic.is_descending_key

WHEN1THEN" DESC"

ELSE""

END

FROM sys.index_columns ic

JOIN sys.columns 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

ORDERBY ic.key_ordinal

FOR XML PATH("")) AS ikey(cols)

OUTER APPLY (SELECT", "+ c.name

FROM sys.index_columns ic

JOIN sys.columns 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

ORDERBY ic.index_column_id

FOR XML PATH("")) AS inc(cols)

WHERE o.name =@tbl

AND i.type IN (1, 2)

ORDERBY o.name, i.index_id

 

以上是 表包含全部索引的索引结构 的全部内容, 来源链接: utcz.com/z/534893.html

回到顶部