SQL SQL looks for column name and schema

Question

Hey. I am trying to execute a query that will place all columns with the word “PURPOSE” in it against several schemas and tables. The code I'm using is:

select distinct table_name, column_name

   from all_tab_columns 

where column_name like '%APPOINTMENT%'

Which works great and tells me the table name and column name.

The problem is that there are more than 90 schemas that I have to run to search in order to find the table name. Is there a way to add to my query that will display the schema name, table name and column name?

Answer 1

Just add OWNER to your selection list columns:

    select distinct owner, table_name, column_name

from all_tab_columns

where column_name like '%APPOINTMENT%'

Answer 2

select distinct owner, table_name, column_name
from all_tab_columns
where column_name like '%APPOINTMENT%'