导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

#####如何通过查询系统表得到某表的所有唯一索引列和唯一约束列?#####

bluelark 2003-01-08 09:34:26
如何通过查询系统表得到某表的所有唯一索引列和唯一约束列?
请给出脚本,很急,非常感谢!!!

马上给分。
...全文
27 点赞 收藏 10
写回复
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
bluewatermark 2003-01-08
declare @TblName varchar(30)

set @TblName = '...'

select b.id, b.name, d.name
from sysobjects a, sysindexes b, sysindexkeys c, syscolumns d
where a.name = @TblName and a.id = b.id and INDEXPROPERTY(object_id(@TblName), b.name, 'IsUnique') = 1 and c.id = a.id and c.colid = d.colid and c.indid = b.indid and d.id = a.id
回复 1
KnowLittle 2003-01-08
你的唯一索引是不是unique constraints?
回复
bluelark 2003-01-08
大力,你的脚本获取的是表中所有列的类型、名称等信息,和唯一索引无关呀?
回复
KnowLittle 2003-01-08
偶来学习,
另外
sysconstrains里面有关于约束的信息

大力兄好像没有提供
回复
alexwoowf 2003-01-08
力王!崇拜........
回复
wgy2008 2003-01-08
学习
回复
glboy 2003-01-08
EXEC sp_helpconstraint 表名
回复
pengdali 2003-01-08
select rtrim(b.name) as colname
,case when h.id is not null then 'PK' else '' end as primarykey
,type_name(b.xusertype) + case when b.colstat & 1 = 1 then '[ID(' + convert(varchar,ident_seed(a.name)) + ',' + convert(varchar,ident_incr(a.name)) + ')]' else '' end as type
,b.length
,case b.isnullable when 0 then 'N' else 'Y' end as [isnull]
,isnull(e.text,'') as [default]
,isnull(c.value,'') as descript
from sysobjects a,syscolumns b
left outer join sysproperties c on b.id = c.id and b.colid = c.smallid
left outer join syscomments e on b.cdefault = e.id
left outer join (select g.id,g.colid from sysindexes f,sysindexkeys g where f.id = g.id and f.indid = g.indid and f.indid > 0 and f.indid < 255 and (f.status & 2048)<>0) h on b.id = h.id and b.colid = h.colid
where a.id = b.id
and a.id = object_id('titles') --tablename改成你要导出的表的名称
order by b.colid
回复
KnowLittle 2003-01-08
我知道了,等一下就给代码
回复
KnowLittle 2003-01-08
有点难度,可以找出pk和constraint,但是要定位到列,好像不行,让我再想想
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告