searching in all tables for a column name

[erixire]

Hi everybody,

I want to know if it's possible in sqlplus to output a list of all the tables which contains a certain column name. It' because I have a lot of tables with the same column name and I want to know the name of every table which has it.

Thanks

 

[Turkbear]

You can query the ALL_TAB_COLUMNS view...

If you do a break on table_name before issuing the query, it will look nicer..

Here's a small example

SQL> break on table_name
select table_name,column_name from all_tab_columns
where table_name like 'HR_C%'
TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
HR_CART_READ                   EMPLOYEE_ID
                               ORGANIZATION_ID
                               USERID
                               FIRST_NAME
                               LAST_NAME
                               TITLE
                               EMAIL
                               PHONE
                               FAX
                               IS_TERMINATED
                               MAILSTOP

hth,

 

[kindus]

I think you might have figured out the answer you were looking for. Anyway, the correct sql would have been

select owner,table_name
from all_tab_columns
where column_name='YOUR_COLUMN_NAME';