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.
THANK you for posting the SQL. I spent quite a bit of time trying to figure out how to do it and finally saw your post. It worked perfectly!!!ReplyDelete
Thank you...will be of great helpReplyDelete
Thank You a lot !!!!ReplyDelete