Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (20.3k points)

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.

1 Answer

0 votes
by (40.7k points)

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;

Related questions

0 votes
2 answers
0 votes
2 answers
0 votes
1 answer
0 votes
1 answer

31k questions

32.8k answers

501 comments

693 users

Browse Categories

...