Find Tables with Specific Field Names

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.