oracle - 查找oracle sql View 中使用的表和列

Question

有没有简单的方法可以找到用于创建 View 的表和列？

我打开了所有 View 并试图找到其中使用的表和列，但我觉得我可能错过了一些东西。是否有任何命令可以显示 View 中使用的所有列和表？

Answer 1

打开 View 。

打开“依赖关系”面板。

这是我的 table 列表。

或者...没错，您也想要列。

对 SQL 工作表中的 View 执行 SELECT * FROM。将鼠标悬停在 SELECT 关键字上。

这会调用数据库中的 SQL 文本扩展功能。请注意，如果您的基础 SQL 包含通配符，则生成 DDL 将无法帮助您处理列名称。

如果我在鼠标悬停后单击查看源代码的文本，您将在工作表中获得实际的 SQL。

例如，这就是我从“SELECT * FROM EMP_DETAILS_VIEW”中得到的结果

SELECT "A1"."EMPLOYEE_ID"        "EMPLOYEE_ID",
       "A1"."JOB_ID"             "JOB_ID",
       "A1"."MANAGER_ID"         "MANAGER_ID",
       "A1"."DEPARTMENT_ID"      "DEPARTMENT_ID",
       "A1"."LOCATION_ID"        "LOCATION_ID",
       "A1"."COUNTRY_ID"         "COUNTRY_ID",
       "A1"."FIRST_NAME"         "FIRST_NAME",
       "A1"."LAST_NAME"          "LAST_NAME",
       "A1"."SALARY"             "SALARY",
       "A1"."COMMISSION_PCT"     "COMMISSION_PCT",
       "A1"."DEPARTMENT_NAME"    "DEPARTMENT_NAME",
       "A1"."JOB_TITLE"          "JOB_TITLE",
       "A1"."CITY"               "CITY",
       "A1"."STATE_PROVINCE"     "STATE_PROVINCE",
       "A1"."COUNTRY_NAME"       "COUNTRY_NAME",
       "A1"."REGION_NAME"        "REGION_NAME"
  FROM (
       SELECT "A7"."EMPLOYEE_ID"        "EMPLOYEE_ID",
              "A7"."JOB_ID"             "JOB_ID",
              "A7"."MANAGER_ID"         "MANAGER_ID",
              "A7"."DEPARTMENT_ID"      "DEPARTMENT_ID",
              "A6"."LOCATION_ID"        "LOCATION_ID",
              "A4"."COUNTRY_ID"         "COUNTRY_ID",
              "A7"."FIRST_NAME"         "FIRST_NAME",
              "A7"."LAST_NAME"          "LAST_NAME",
              "A7"."SALARY"             "SALARY",
              "A7"."COMMISSION_PCT"     "COMMISSION_PCT",
              "A6"."DEPARTMENT_NAME"    "DEPARTMENT_NAME",
              "A5"."JOB_TITLE"          "JOB_TITLE",
              "A4"."CITY"               "CITY",
              "A4"."STATE_PROVINCE"     "STATE_PROVINCE",
              "A3"."COUNTRY_NAME"       "COUNTRY_NAME",
              "A2"."REGION_NAME"        "REGION_NAME"
         FROM "HR"."EMPLOYEES"      "A7",
              "HR"."DEPARTMENTS"    "A6",
              "HR"."JOBS"           "A5",
              "HR"."LOCATIONS"      "A4",
              "HR"."COUNTRIES"      "A3",
              "HR"."REGIONS"        "A2"
        WHERE "A7"."DEPARTMENT_ID" = "A6"."DEPARTMENT_ID"
          AND "A6"."LOCATION_ID"    = "A4"."LOCATION_ID"
          AND "A4"."COUNTRY_ID"     = "A3"."COUNTRY_ID"
          AND "A3"."REGION_ID"      = "A2"."REGION_ID"
          AND "A5"."JOB_ID"         = "A7"."JOB_ID"
) "A1";