SQL Oracle search for column name and schema

Question

Hi I am trying to do a query that will locate all the columns with the word 'APPOINTMENT' in it against multiple schemas and tables. The code I am using is :

select distinct table_name, column_name

   from all_tab_columns 

where column_name like '%APPOINTMENT%'

Which works fine and tells me the Table name and the column name.

The problem is that there are over 90 schemas that I have to search through to find the table name. Is there a way I can add to my query that will display the schema name, table name and column name?

Answer 1

Just add OWNER to your select 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%'