2

Is there any easy way to find the tables and columns used to create a view ?

i opened all the views and tried to find the tables and column used in it, but I feel I might have missed something. Is there any commands that displays all the columns and tables used in a view ?

siddharth thiagarajan
  • 27
  • 1
  • 6
  • Hi, there are a lot of ways to do this with SQL Developer. Here is a similar post with some reasonable and good answers, https://stackoverflow.com/questions/23515801/get-view-ddl-using-query – Patrick Bacon Feb 21 '20 at 19:54

2 Answers2

5

Open the view.

Open the Dependencies panel.

enter image description here

There's my list of tables.

Or...that's right, you wanted COLUMNS as well.

Do a SELECT * FROM for your view in the SQL Worksheet. Mouse over the SELECT keyword.

enter image description here

This invokes the SQL Text Expansion feature in the database. Note that if your underlying SQL includes wildcards, generating the DDL won't help you with column names.

If I click on the text of the view source after the mouse-over, you'll get the actual SQL in your worksheet.

For example this is what I get for '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";
thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120
0

select * from all_dependencies where type = 'VIEW' and name = :your_view