如何找到Oracle视图的基础列和表名? [英] How can I find the underlying column and table name for an Oracle view?

问题描述

这听起来应该很简单，但事实并非如此！我找不到Oracle(元数据)视图，该视图提供了Oracle视图列的基础列和表名.我发现这样做的唯一方法是解析视图源SQL(这远非精确科学).

This sounds like it should be simple to do but not so! There is no Oracle (meta-data) view that I can find that gives the underlying column and table name for an Oracle view column. The only way I have found of doing it is to parse the view source SQL (which is far from and exact science).

只是为了解释我想要的内容，请考虑以下在SCOTT模式中创建的示例视图:

Just to explain what I want, consider the following example view which I created in SCOTT schema:

CREATE OR REPLACE VIEW EMP_DEP
(
   EMPLOYEE_NAME,
   DEPARTMENT_NAME
)
AS
SELECT 
   ENAME,
   DNAME 
FROM
   emp a,
   dept b
WHERE
   a.deptno= b.deptno
/

现在给定视图和列名称EMP_DEP.DEPARTMENT_NAME，我想获取视图使用的基础表和列名称，即DEPT.DNAME.有谁知道一种不涉及解析视图的SQL的方式来获取此信息的方法?

Now given the view and column name EMP_DEP.DEPARTMENT_NAME, I would like to get the underlying table and column name used by the view which is DEPT.DNAME. Does anyone know of a way to get this information that does not involve parsing the view's SQL?

推荐答案

由于请求者正在查找其表的任何实例，而不是针对特定视图，因此我建议:

Since the requester is looking for any instance of his table, and not with respect to a specific view, I would recommend:

SELECT *
  FROM DBA_DEPENDENCIES
 WHERE TYPE = 'VIEW'
   AND REFERENCED_TYPE = 'TABLE'
   AND REFERENCED_NAME = '<TABLE_NAME>'

这篇关于如何找到Oracle视图的基础列和表名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！