How to search a column name within all tables of a database

Question

I'M TRYING to find a column called author_last_name in oracle-apex I want to find a column in my schema is it possible to search for a column or do i have to look through every table? i have at least 50 tables in my schema
select table_name,column_name from cihe.columns where column_name like '%author%'

possible duplicate of [I want to show all tables that have specified column name](http://stackoverflow.com/questions/4197657/i-want-to-show-all-tables-that-have-specified-column-name) — Brian Robbins, Jul 31 '14 at 15:08

score 8 · Accepted Answer · answered Jul 31 '14 at 15:08

8

select table_name, column_name
from user_tab_columns
where column_name like '%AUTHOR%';

All system views are documented here: http://docs.oracle.com/cd/E11882_01/server.112/e25513/toc.htm

answered Jul 31 '14 at 15:08

score 1 · Answer 2 · answered Jul 31 '14 at 15:08

1

Use oracle data dictionary:

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

answered Jul 31 '14 at 15:08

neshkeev

6,280
3
26
47

you have to do it like'%TABLE%' /\ || – user3841039 Jul 31 '14 at 15:30
yes, right, I didn't understand it, I thought you wanted to find out the table name based on a full column name. – neshkeev Jul 31 '14 at 16:44

How to search a column name within all tables of a database

2 Answers2