How to search a column name within all tables of a database at specific schema

Question

select table_name, column_name
from all_tab_columns 
where column_name = '<YOUR_COLUMN_HERE_IN_CAPITAL_CASE>'

How can I use the above columns search query for a specific schema at Oracle DB?

I am asking because I have tried the query and it also returned many tables outside of the schema I am interested into, so they are of no interest to me.

Answer 1

select table_name, column_name
FROM all_tab_columns 
where column_name = '<YOUR_COLUMN_HERE_IN_CAPITAL_CASE>' 
  and owner = '<schema>';

all_tab_columns contains all the columns on which the current user has privileges. So it may not return all the data.

dba_tab_columns contains information about all columns, but you may need some special privileges to query this dictionary view.

And finally, if you're interested only in the columns of all tables owned by the current user you can use:

select table_name, column_name
FROM user_tab_columns 
where column_name = '<YOUR_COLUMN_HERE_IN_CAPITAL_CASE>';

But this view doesn't have an OWNER column (it only contains all the columns owned by the current user)