I have a situation where I have to find related values in a table without knowing the corresponding table to run the query against.
I have discussed with a colleague who explained that the primary key and foreign key concept may not help because of the way the DBA designed the database objects
Toad doesn't look through table data to try to make suggestions. So if you don't have foreign keys, your options are limited.
You might get some clues if you go to Schema Browser, then pick your table, then go to RHS "Used By" tab. If there, you see a package or procedure that you think might use both tables, then you could F4 on the package/procedure - that will open a dialog with info about the procedure, and there is a "Uses" tab where you could see other objects that the procedure/package uses.
Just a thought. Again, your options are pretty limited w/o foreign keys.
Thanks John for your quick response
Hi John,
A.fter the F4 what tab did you use to get the final view. I dont get a similar view like you shared
From the table:
Thank you. I have located it. It is not shown as you displayed on your screen.
In that case, I guess the package does not use any other tables. It was kind of a shot in the dark.
If you know what column names you are looking for, you could run a SQL like this in the editor to find the tables or views that have columns by that name:
select *
from dba_tab_columns -- you might have to use all_tab_columns here instead
where owner = 'X' -- if you know it
and column_name = 'Y'
Getting into the thread a little late here, but what John says is correct... without the referential definitions between tables, and without knowing which columns your desired values might reside in, you'll have to resort to looking through the data values in your tables directly.
There is one optional module that could help your use case, and it's called the Sensitive Data Protection module (extra license-able plug-in... talk to your Quest sales rep for more info) . That module introduces a pre-defined set of rules by which you can search through your database for any sensitive data, either by column name or by values within the columns you want to search in. Although not designed for your use case, you can define a search rule that looks for your code value (1001) in the tables from any schema/s, and Toad can generate a report of what it finds. See snapshot below, where I defined a rule ("Lookup200") that looks for any columns that contain a code value of "200". The result set shows a number of columns in tables where this value was found. Without something like this, you'll need to construct your own set of SQL to rummage through the data values of your tables to find what you're looking for.
@JohnDorlon the all_tab_columns suggestion is a good one. @mjsystemss if you know the column name(s) that might contain the value you are looking for, you can start at all_tab_columns and make a table containing all of the table names and column names that might have that value. then use Toad's query iterator (in Automation Designer) to input those table/column names as variables into a query with the column value you're interested in as part of the where clause. insert any results back into a different table and you should be pretty close to your goal.
