Search an Oracle database for tables with specific column names?

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.

Tony Andrews · Accepted Answer · 2016-03-03T17:35:49.840

272

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 owner, table_name, column_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;

edited Mar 03 '16 at 17:35

answered Dec 23 '09 at 15:06

Tony Andrews

129,880
21
220
259

4

You should probably use DBA_TAB_COLUMNS instead of ALL_TAB_COLUMNS to do this search, in case the user you're logging in as doesn't have access to some tables. – Jeffrey Kemp Dec 24 '09 at 02:07
True, but only if the user you are connected as has SELECT ANY TABLE privilege. – Tony Andrews Dec 24 '09 at 10:07
2

Add `column_name` + `like` if you're not sure of the exact name: `select owner, table_name, column_name from all_tab_columns where column_name like 'someField%';` – Mike R Jan 06 '15 at 19:59
I have the same issue, and I have used the answer select owner, table_name from all_tab_columns where column_name = 'ID'; But if I do a select * from one of the resulted table_names the error is table or view does not exist @TonyAndrews – Andreea Onica Dec 06 '22 at 07:41
1

@AndreeaOnica all_tab_columns returns columns from tables in your own schema and others that you can access. If a table returned is in a different schema, then unless there is a synonym for that table you need to prefix it e.g. `select * from otherschema.tablename;`. Could that be the problem? – Tony Andrews Dec 07 '22 at 08:43

score 19 · Answer 2 · edited Jan 13 '14 at 09:29

19

TO search a column name use the below query if you know the column name accurately:

select owner,table_name from all_tab_columns where upper(column_name) =upper('keyword');

TO search a column name if you dont know the accurate column use below:

select owner,table_name from all_tab_columns where upper(column_name) like upper('%keyword%');

edited Jan 13 '14 at 09:29

kleopatra

51,061
28
99
211

answered Jan 13 '14 at 09:21

user3141191

191
1
5

upper is important – Amrit Jan 05 '21 at 18:32

JosephStyons · Answer 3 · 2009-12-23T15:08:36.910

13

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%'

edited Dec 23 '09 at 15:08

answered Dec 23 '09 at 14:56

JosephStyons

57,317
63
160
234

How do I use this to look at a different schema? (I forgot to mention that req in my original question) – David Oneill Dec 23 '09 at 14:58
Just prepend the schema name onto the front of each table name...i.e. myschema.c1. Obviously you must have select privileges on the other schem – wadesworld Dec 23 '09 at 15:06
`SELECT * FROM COLS` returns nothing from my schema. I only have select privileges on the tables. Would select not be enough to allow me to view it via COLS? – David Oneill Dec 23 '09 at 15:09
`select * from schema1.cols` gives me a `table or view does not exist` error. Would that have to do with how the privileges are set up? – David Oneill Dec 23 '09 at 15:13
Yes, it would. It seems like Tony Andrew's answer is probably better for your situation. I had forgotten about the "all_tab_columns" view. – JosephStyons Dec 24 '09 at 13:12
The code example underneath: "This one will give you a list of tables that have all of the columns you want:" was extremely helpful for me. Ty! – Eric Milliot-Martinez Feb 14 '17 at 18:06

score 1 · Answer 4 · answered Dec 24 '09 at 16:09

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

Search an Oracle database for tables with specific column names?

4 Answers4

Linked

Related