select t.tabname,c.colname,
case s.constrtype when ‘C’ then ‘检查’ when ‘N’ then ‘非空’ when ‘P’ then ‘主键’ when ‘R’ then ‘引用’ when ‘T’ then ‘表’ when ‘U’ then ‘唯一’ else ‘无’ end constraint
,s.idxname
from systables t
inner join syscolumns c on c.tabid = t.tabid
left join syscoldepend d on c.tabid = d.tabid and c.colno=d.colno
left join sysindexes i on t.tabid=i.tabid
and (c.colno=i.part1 or c.colno=i.part2 or c.colno=i.part3 or c.colno=i.part4 or c.colno=i.part5
or c.colno=i.part6 or c.colno=i.part7 or c.colno=i.part8 or c.colno=i.part9 or c.colno=i.part10
or c.colno=i.part11 or c.colno=i.part12 or c.colno=i.part13 or c.colno=i.part14 or c.colno=i.part15 or c.colno=i.part16)
left join sysconstraints s on s.idxname = i.idxname or d.constrid = s.constrid
where t.tabname = ‘t_event’
只查询单主键的方法
create table t(fi int not null,fv varchar(200));
alter table t add constraint (primary key (fi) constraint pk_t);
create table tt(fii int not null primary key,fvv varchar(100));
select t.tabid,t.tabname,c.constrid,c.constrname,i.idxname,co.colno,co.colname,co.coltype
from systables t,sysconstraints c,sysindexes i,syscolumns co
where t.tabid=c.tabid
and t.tabid=i.tabid
and c.idxname=i.idxname
and c.tabid=co.tabid
and i.part1=co.colno
and t.tabid>99
and c.constrtype=‘P’;
