For a list of tables with a specific column name, you can query the DBA_TAB_COLUMNS table:

select table_name
from dba_tab_columns
where column_name=’MY_COLUMN_NAME’
order by table_name asc;

This is helpful for when you are looking for any other tables that may JOIN on a specific column, assuming you follow the convention that the column name is the same (or similar) in other tables.

If you’re doing prefixes on column names, such as “PK_” or “FK_”, just use a LIKE comparison:

select table_name
from dba_tab_columns
where column_name like ‘%K_MY_COLUMN_NAME’
order by table_name asc;

That will select anything starting with “PK_” or “FK_”, but without the complete wildcard at the front of the column name.