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.
ベストアンサー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;