oracle 如何在特定模式的数据库的所有表中搜索列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27128372/
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
How to search a column name within all tables of a database at specific schema
提问by Kostas75
select table_name, column_name
from all_tab_columns
where column_name = '<YOUR_COLUMN_HERE_IN_CAPITAL_CASE>'
How can I use the above columns search query for a specific schema at Oracle DB?
如何在 Oracle DB 中对特定架构使用上述列搜索查询？
I am asking because I have tried the query and it also returned many tables outside of the schema I am interested into, so they are of no interest to me.
我问是因为我已经尝试过这个查询，它也返回了我感兴趣的模式之外的许多表，所以我对它们不感兴趣。
回答by Multisync
select table_name, column_name
FROM all_tab_columns
where column_name = '<YOUR_COLUMN_HERE_IN_CAPITAL_CASE>'
and owner = '<schema>';
all_tab_columnscontains all the columns on which the current user has privileges. So it may not return all the data.
all_tab_columns包含当前用户拥有权限的所有列。所以它可能不会返回所有数据。
dba_tab_columnscontains information about all columns, but you may need some special privileges to query this dictionary view.
dba_tab_columns包含有关所有列的信息，但您可能需要一些特殊权限才能查询此字典视图。
And finally, if you're interested only in the columns of all tables owned by the current user you can use:
最后，如果您只对当前用户拥有的所有表的列感兴趣，您可以使用：
select table_name, column_name
FROM user_tab_columns
where column_name = '<YOUR_COLUMN_HERE_IN_CAPITAL_CASE>';
But this view doesn't have an OWNERcolumn (it only contains all the columns owned by the current user)
但是这个视图没有OWNER列（它只包含当前用户拥有的所有列）
回答by tvCa
You can use the LIKE function to refine a search. I don't know the exact names of the columns, but you could add the owner and the table name to this one:
您可以使用 LIKE 函数来优化搜索。我不知道列的确切名称，但您可以将所有者和表名添加到这一列：
select table_name, column_name
from all_tab_columns
where column_name = '<YOUR_COLUMN_HERE_IN_CAPITAL_CASE>'
in a way similar to this:
以类似于此的方式：
select column1,column2,column3,more_columns
from all_tables_you_need
where column1 like '%&column1%'
and column2 like '%&column2%'
and column3 like '%&column3%';
Either of those 3 variables can also be empty, but you can also put a very specific one in there, so that the result will vary a lot. Behold use of the UPPER function, if you are not sure of the case, or you want to ignore case sensitivity.
这 3 个变量中的任何一个也可以为空，但您也可以在其中放置一个非常具体的变量，这样结果就会有很大差异。看看 UPPER 函数的使用，如果您不确定大小写，或者您想忽略区分大小写。
回答by Dulith De Costa
Use the following.
使用以下内容。
You can try this via SQL toolthat is used by you
您可以通过您使用的SQL 工具尝试此操作
select table_name from all_tab_columns where column_name = 'PICK_COLUMN';
Or if you have DBA privileges,
或者，如果您有DBA 权限，
select table_name from dba_tab_columns where column_name = 'PICK_COLUMN';
But if you are not sure about the column names you can add LIKEstatements to current query.
但是，如果您不确定列名，则可以LIKE向当前查询添加语句。
Eg:
例如：
select table_name from all_tab_columns where column_name LIKE '%PICK_COLUMN%';
