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