sysindexkeys 包含索引中的键或列的信息。该表存储在每个数据库中。列名 数据类型 描述 id int 表 ID indid smallint 索引 ID colid smallint 列 ID keyno smallint 该列在索引中的位置
可這樣生成,這是之前寫的統計索引方法,你可查看ColName為你要的效果with Index1 as ( select top 100 percent row_number()over(partition by a.Name order by b.index_id) as ID,object_Name(a.object_id) as TableName,a.Name as IndexName,c.Name as ColName, description=a.type_desc,a.is_unique,a.is_primary_key,a.is_unique_constraint,OrderNr=CASE WHEN b.is_descending_key=0 THEN 'Asc' ELSE 'DESC' END from sys.indexes a join sys.index_columns b on a.Object_id=b.Object_id and a.index_id=b.index_id join sys.columns c on c.object_id=a.object_id and c.column_id=b.column_id where objectproperty(a.object_id,'IsUserTable')=1 and a.Object_id<>object_id('dtproperties') order by TableName,a.Name ) ,index2 as ( select TableName,IndexName,ColName,is_unique,is_primary_key,is_unique_constraint,description from (select distinct TableName,IndexName,is_unique,is_primary_key,is_unique_constraint,description from Index1)a CROSS apply (select ColName=stuff((select ','+ColName +' '+OrderNr from Index1 where TableName=a.TableName and IndexName=a.IndexName order by ID for xml PATH('')),1,1,''))b ) select * from index2 order by TableName,IndexName
--结合sys.indexes和sys.index_columns,sys.objects,sys.columns查询索引所属的表或视图的信息 select o.name as 表名, i.name as 索引名, c.name as 列名, i.type_desc as 类型描述, is_primary_key as 主键约束, is_unique_constraint as 唯一约束, is_disabled as 禁用 from sys.objects o inner join sys.indexes i on i.object_id=o.object_id inner join sys.index_columns ic on ic.index_id=i.index_id and ic.object_id=i.object_id inner join sys.columns c on ic.column_id=c.column_id and ic.object_id=c.object_id go--查询索引的键和列信息 select o.name as 表名, i.name as 索引名, c.name as 字段编号, from sysindexes i inner join sysobjects o on i.id=o.id inner join sysindexkeys k on o.id=k.id and i.indid=k.indid inner join syscolumns c on c.id=i.id and k.colid=c.colid where o.name='表名'
包含索引中的键或列的信息。该表存储在每个数据库中。列名 数据类型 描述
id int 表 ID
indid smallint 索引 ID
colid smallint 列 ID
keyno smallint 该列在索引中的位置
as
(
select
top 100 percent row_number()over(partition by a.Name order by b.index_id) as ID,object_Name(a.object_id) as TableName,a.Name as IndexName,c.Name as ColName,
description=a.type_desc,a.is_unique,a.is_primary_key,a.is_unique_constraint,OrderNr=CASE WHEN b.is_descending_key=0 THEN 'Asc' ELSE 'DESC' END
from
sys.indexes a
join
sys.index_columns b on a.Object_id=b.Object_id and a.index_id=b.index_id
join
sys.columns c on c.object_id=a.object_id and c.column_id=b.column_id
where
objectproperty(a.object_id,'IsUserTable')=1 and a.Object_id<>object_id('dtproperties')
order by TableName,a.Name
)
,index2
as
(
select
TableName,IndexName,ColName,is_unique,is_primary_key,is_unique_constraint,description
from
(select distinct TableName,IndexName,is_unique,is_primary_key,is_unique_constraint,description from Index1)a
CROSS apply
(select ColName=stuff((select ','+ColName +' '+OrderNr from Index1 where TableName=a.TableName and IndexName=a.IndexName order by ID for xml PATH('')),1,1,''))b
)
select
*
from
index2
order by TableName,IndexName
select
o.name as 表名,
i.name as 索引名,
c.name as 列名,
i.type_desc as 类型描述,
is_primary_key as 主键约束,
is_unique_constraint as 唯一约束,
is_disabled as 禁用
from
sys.objects o
inner join
sys.indexes i
on
i.object_id=o.object_id
inner join
sys.index_columns ic
on
ic.index_id=i.index_id and ic.object_id=i.object_id
inner join
sys.columns c
on
ic.column_id=c.column_id and ic.object_id=c.object_id
go--查询索引的键和列信息
select
o.name as 表名,
i.name as 索引名,
c.name as 字段编号,
from
sysindexes i inner join sysobjects o
on
i.id=o.id
inner join
sysindexkeys k
on
o.id=k.id and i.indid=k.indid
inner join
syscolumns c
on
c.id=i.id and k.colid=c.colid
where
o.name='表名'
俺还在用SQL200