在Oracle数据库中搜索具有特定列名的表? [英] Search an Oracle database for tables with specific column names?
问题描述
我们有一个包含许多表的大型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?
IE会向我显示所有具有以下列的表:id, fname, lname, address
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';
要查找具有4列中的任何一列或全部的表:
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');
要查找具有全部4列(不丢失)的表:
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;
这篇关于在Oracle数据库中搜索具有特定列名的表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!