SQL 在 Oracle 数据库中搜索具有特定列名的表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1953239/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Search an Oracle database for tables with specific column names?
提问by David Oneill
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
IE 显示所有具有列的表： 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.
我忘记添加的细节：我需要能够搜索不同的模式。我必须用来连接的那个不拥有我需要搜索的表。
回答by Tony Andrews
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;
回答by JosephStyons
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 allof 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%'
回答by user3141191
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%');
回答by Doug Porter
Here is one that we have saved off to findcol.sql so we can run it easily from within SQLPlus
这是我们保存到 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
