Oracle SQL 如何找出哪个表有以下两列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6658696/
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
Oracle SQL how to find out which table has the following two columns?
提问by Oh Chin Boon
I know of a statement that I can issue to find out the table with the column I am looking for:
我知道我可以发出一个语句来找出包含我要查找的列的表：
SELECT DISTINCT(table_name)
FROM all_tab_cols
WHERE column_name = 'EMP_ID';
However, how should I modify this so that I may find the table that has both say EMP_IDand EMP_NAME?
但是，我应该如何修改它以便我可以找到同时包含 sayEMP_ID和的表EMP_NAME？
回答by OMG Ponies
Use:
用：
SELECT table_name
FROM all_tab_cols
WHERE column_name IN ('EMP_ID', 'EMP_NAME')
GROUP BY table_name
HAVING COUNT(DISTINCT column_name) = 2
The count comparison must equal the number of parameters defined in the INclause.
计数比较必须等于IN子句中定义的参数数量。
...but this is a safer means of determining the table in case their are duplicates:
...但这是确定表格的一种更安全的方法，以防它们重复：
SELECT table_name
FROM all_tab_cols
WHERE column_name IN ('EMP_ID', 'EMP_NAME')
GROUP BY table_name, owner
HAVING COUNT(column_name) = 2
