Search an Oracle database for tables with specific column names?

Question

We have a large Oracle database with many tables. Is there a way I can query or search to find if there are any tables with certain column names?

IE shows me all tables that have the columns: id, fname, lname, address

The detail I forgot to add: I need to be able to search through different schemas. The one I must use to connect doesn't own the tables I need to search through.

Answer 1

Try this code, to find all the tables with a particular column:

select owner, table_name from all_tab_columns where column_name = 'ID';

Try using the code given below, to find tables that have any or all of the 4 columns:

select owner, table_name, column_name

from all_tab_columns

where column_name in ('ID', 'FNAME', 'LNAME', 'ADDRESS');

Use the code given below to find tables that have all 4 columns (with none missing):

select owner, table_name

from all_tab_columns

where column_name in ('ID', 'FNAME', 'LNAME', 'ADDRESS')

group by owner, table_name

having count(*) = 4;