Search an Oracle database for tables with specific column names?

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.

337990 次浏览
小开

您需要的数据在“ protocol”元数据表中:

SELECT * FROM COLS WHERE COLUMN_NAME = 'id'

这个列表将提供一个表列表，其中包含所需列的 所有:

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

要在不同的架构中执行此操作，只需在表前指定架构，如

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

如果您想将多个模式的搜索结果合并到一个输出结果中，那么您可以这样做:

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%'
小开
最佳答案

查找具有特定列的所有表:

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

若要查找包含4列中的任意一列或全部列的表:

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;
小开

下面是我们保存到 findcol.sql 中的一个，因此我们可以在 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
小开

如果您准确地知道列名，可以使用下面的查询来搜索列名:

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

如果您不知道准确的列名，请在下面搜索列名:

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

提交答案