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 show me all tables that have the columns: id, fname, lname, address

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

To find all tables with a particular column:

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

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'); 

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; 

Answer 2

TO search a column name use the below query if you know the column name accurately:

select owner,table_name from all_tab_columns where upper(column_name) =upper('keyword'); 

TO search a column name if you dont know the accurate column use below:

select owner,table_name from all_tab_columns where upper(column_name) like upper('%keyword%');