--查看表的索引信息 exec sp_helpindex tb--结合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='表名'
exec sp_helpindex tb--结合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='表名'
主要就是 sysobjects 和sysindexes表里面的。
我给你介绍个系统函数 sp_MSforeachtable ,
也许你在以后会用到。
--查询所有的表
CREATE TABLE #temp (tablename VARCHAR(60))
INSERT #temp
exec sp_MSforeachtable @command1="select '?'"
SELECT * FROM #temp
sp_MSforeachtable