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 PSRECFIELD B
WHERE A.RECNAME = B.RECNAME
AND B.FIELDNAME = 'EMPLID')
AND EXISTS (SELECT 'X'
FROM PSRECFIELD 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
Author Info
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 Chris
Product
PeopleSoft Simple Web Services (SWS)
Do you need a quick way to create web services?

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.

Book
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.