Find Tables with Specific Field Names
By Chris Malek | Wed, Oct 13, 2010
Have you ever wanted to find a table that had specific combination of fields on it? This is a common occurrence when looking for a run control record to use with a new process or looking for specific tables in a module you are not familiar with. A simple SQL statement can aid in this searching.
SELECT * FROM PSRECDEFN A
WHERE RECTYPE = 0
AND EXISTS (SELECT 'X'
FROM PSRECFIELDDB B
WHERE A.RECNAME = B.RECNAME
AND B.FIELDNAME = 'EMPLID')
AND EXISTS (SELECT 'X'
FROM PSRECFIELDDB B
WHERE A.RECNAME = B.RECNAME
AND B.FIELDNAME = 'LOCATION')
ORDER BY RECNAME
In this example I am searching for tables (RECTYPE = 0) that have both an EMPLID and LOCATION filed. If you have more than two fields you can add another exists clause. If you are looking for key fields you can do something similar with the PSKEYDEFN table. It defines the keys on a table.
Article Categories
Chris Malek
Chris Malek is a PeopleTools® Technical Consultant with two decades of experience working on PeopleSoft enterprise software projects. He is available for consulting engagements.
About Chris Work with ChrisPeopleSoft Simple Web Services (SWS)
Introducing a small but powerful PeopleSoft bolt-on that makes web services very easy. If you have a SQL statement, you can turn that into a web service in PeopleSoft in a few minutes.
Integration Broker - The Missing Manual
I am in the process of writing a book called "Integration Broker - The Missing Manual" that you can read online.