Query for primary keys on a PeopleSoft table

13 Oct 2014 →

My day job has me building Rails apps that sling data to and from PeopleSoft instances. Because of this, I often find myself looking up a particular table’s primary key. If you ever had to do this with a PeopleSoft table, you know it’s a pain. PeopleSoft doesn’t use real DB keys, so to look these up you have to ask PeopleSoft itself. For me, this always involved logging into the web application and fiddling with the reporting engine or worse, firing up a VM with App Designer.

I’ve just stumbled upon the table where PeopleSoft stores this information, pskeydefn. So to query the primary keys for say, ps_acad_prog_tbl, query like so:

SELECT fieldname
FROM pskeydefn
WHERE recname='ACAD_PROG_TBL' -- or any PS table
  and indexid='_'
ORDER BY keyposn

This is huge time saver for me. If anyone else lives in this strange world between web apps and PS, I hope this is useful.