在Oracle数据库中搜索具有特定列名的表?

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?

我们有一个包含许多表的大型Oracle数据库。是否有一种方法可以查询或搜索是否有任何具有特定列名的表?

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

我给我看所有有列的表: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.

我忘记添加的细节:我需要能够搜索不同的模式。我必须用来连接的那个表并不拥有我需要搜索的表。

5 个解决方案

#1

158

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:

查找包含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):

查找所有4列(无遗漏)的表:

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

#2

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

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

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

要在不同的模式中执行此操作，只需在表前面指定模式，如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%'

#3

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

#4

select table_name from dba_tab_columns where column_name='THE_COLUMN_YOU_LOOK_FOR';

从dba_tab_columns中选择table_name，其中column_name='THE_COLUMN_YOU_LOOK_FOR';

#5

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

这是我们保存下来的芬考。因此，我们可以在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

#1

158