Oracle column query or search

Question

We have a large Oracle database with many tables. Is there a way I can query or search to find if there are any tables with certain column names?

IE show me all tables that have the columns: id, fname, lname, address

Detail I forgot to add: I need to be able to search through different schemas. The one I must use to connect doesn't own the tables I need to search through.

Answer 1

The data you want is in the "cols" meta-data table:

SELECT * FROM COLS WHERE COLUMN_NAME = 'id'

This one will give you a list of tables that have all of the columns you want:

select distinct
  C1.TABLE_NAME
from
  cols c1
  inner join
  cols c2
  on C1.TABLE_NAME = C2.TABLE_NAME
  inner join
  cols c3
  on C2.TABLE_NAME = C3.TABLE_NAME
  inner join
  cols c4
  on C3.TABLE_NAME = C4.TABLE_NAME  
  inner join
  tab t
  on T.TNAME = C1.TABLE_NAME
where T.TABTYPE = 'TABLE' --could be 'VIEW' if you wanted
  and upper(C1.COLUMN_NAME) like upper('%id%')
  and upper(C2.COLUMN_NAME) like upper('%fname%')
  and upper(C3.COLUMN_NAME) like upper('%lname%')
  and upper(C4.COLUMN_NAME) like upper('%address%')

To do this in a different schema, just specify the schema in front of the table, as in

SELECT * FROM SCHEMA1.COLS WHERE COLUMN_NAME LIKE '%ID%';

If you want to combine the searches of many schemas into one output result, then you could do this:

SELECT DISTINCT
  'SCHEMA1' AS SCHEMA_NAME
 ,TABLE_NAME
FROM SCHEMA1.COLS
WHERE COLUMN_NAME LIKE '%ID%'
UNION
SELECT DISTINCT
  'SCHEMA2' AS SCHEMA_NAME
 ,TABLE_NAME
FROM SCHEMA2.COLS
WHERE COLUMN_NAME LIKE '%ID%'

Answer 2

To find all tables with a particular column:

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

To find tables that have any or all of the 4 columns:

select distinct owner, table_name 
from all_tab_columns 
where column_name in ('ID', 'FNAME', 'LNAME', 'ADDRESS');

To find tables that have all 4 columns (with none missing):

select owner, table_name
from all_tab_columns
where column_name in ('ID', 'FNAME', 'LNAME', 'ADDRESS')
group by owner, table_name
having count(*) = 4;

Answer 3

Here is one that we have saved off to findcol.sql so we can run it easily from within SQLPlus

set verify off
clear break
accept colnam prompt 'Enter Column Name (or part of): '
set wrap off
select distinct table_name, 
                column_name, 
                data_type || ' (' || 
                decode(data_type,'LONG',null,'LONG RAW',null,
                       'BLOB',null,'CLOB',null,'NUMBER',
                       decode(data_precision,null,to_char(data_length),
                              data_precision||','||data_scale
                             ), data_length
                      ) || ')' data_type
  from all_tab_columns
 where column_name like ('%' || upper('&colnam') || '%');
set verify on