How to find table name/s if a column name is known

[Guest_imported]

Can any one help me write a PL/SQL statement that does the following in Oracle 8/8i:

Given a column name, output all the table names that contain that column and also the data type of the column in those tables.

Thanks in advance.

 

[karluk]

select table_name,column_name,data_type from dba_tab_columns
where column_name='MY_COLUMN'

 

[diasf]

I've tried this example, but I can't get any results! Am I missing something? The answer I get is "table or view does not exist". It's pointing to "dba_tab_columns". :-(

Can anyone please help me?

 

[karluk]

The id you are using to execute the query may not have access to "dba_tab_columns". You can try "all_tab_columns" instead, but you may miss some tables. "All_tab_columns" is restricted to tables which your id has privileges to view.

 

[listener22]

try sys.dba_tab_columns Jim

oracle, vb

 

[sugan]

Select table_name from all_tab_columns where
column_name= '<columnname>'

 

[Leon1977]

Beware type the column name with capslock ON
eg With Caps Letters