如何从syscolumns中取出哪个字段是主键？

[myth1979]

如题

[thzok]

/*

脚本内容：生成数据库的所有表结构等信息。

时间：22:55 2003-12-16

*/

SELECT

(case when a.colorder=1 then d.name else '' end) N'表名',

a.colorder N'字段序号',

a.name N'字段名',

(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',

(case when (SELECT count(*)

FROM sysobjects

WHERE (name in

(SELECT name

FROM sysindexes

WHERE (id = a.id) AND (indid in

(SELECT indid

FROM sysindexkeys

WHERE (id = a.id) AND (colid in

(SELECT colid

FROM syscolumns

WHERE (id = a.id) AND (name = a.name))))))) AND

(xtype = 'PK'))>0 then '√' else '' end) N'主键',

b.name N'类型',

a.length N'占用字节数',

COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',

isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',

(case when a.isnullable=1 then '√'else '' end) N'允许空',

isnull(e.text,'') N'默认值',

isnull(g.[value],'') AS N'字段说明'

--into ##tx



FROM syscolumns a left join systypes b

on a.xtype=b.xusertype

inner join sysobjects d

on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'

left join syscomments e

on a.cdefault=e.id

left join sysproperties g

on a.id=g.id AND a.colid = g.smallid

order by object_name(a.id),a.colorder

看看就知道了

[realgz]

要从sysconstraints取。

[zjcxc]

SELECT
表名=case when a.colorder=1 then d.name else '' end,
字段序号=a.colorder,
字段名=a.name
FROM syscolumns a
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
where exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
)))
order by a.id,a.colorder

[qiaolin53]

sp_pkeys 表
查询分析器下得到该表的主键。

[myth1979]

但是，上面的几种方法中，取出的都是表中的主键、外键，有没有办法在他们的基础上区分出哪个列是主键？哪个列是外键？
谢谢各位！

[aierong]

select CONSTRAINT_NAME,TABLE_NAME, CONSTRAINT_TYPE from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where table_name='tablename'

看这个字段就知道约束类型
CONSTRAINT_TYPE

[myth1979]

不好意思，搞错了，出来的都是主键，谢谢大家了。。。。。

[page1980]

to thzok(thzok)
　真不错！！！