Listing Primary keys
Posted in 2000
Topics: General Discussion
Hi all,
One of my programmers asked me for the tables and columns which
makeup the primary keys on our system. (Having just started work here, I
didn't even realize we had them set up. Aside opinion: if no Referential
constraints are being used, shouldn't these primary keys be handled by
Unique indexes and integrity handled by the programs?)
My first response was to run a dbschema and parese out the primary
key lines. However, she needed SQL code to insert into a program. I came up
with the following, but it looks ugly! Does anyone know a better way???
Thanks,
Mike Hoffman
code:
select tab.tabname, col.colname, col.colno
from syscolumns col, sysindexes ind, systables tab
where col.tabid = ind.tabid
and (col.colno=ind.part1 or col.colno=ind.part2 or col.colno=ind.part3
or col.colno=ind.part4 or col.colno=ind.part5 or col.colno=ind.part6
or col.colno=ind.part7 or col.colno=ind.part8 or col.colno=ind.part9
or col.colno=ind.part10 or col.colno=ind.part11 or col.colno=ind.part12
or col.colno=ind.part13 or col.colno=ind.part14
or col.colno=ind.part15 or col.colno=ind.part16)
and ind.idxname in (select idxname from sysconstraints d
where d.constrtype="P")
and ind.tabid = (select tabid from sysconstraints e
where e.constrtype="P" and e.idxname=ind.idxname)
and tab.tabid = col.tabid
order by 1,3