Search This Blog

Monday, October 12, 2009

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

3 comments:

  1. 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
  2. Ilyaad DamreeDecember 02, 2015

    Thank you...will be of great help

    ReplyDelete