List all table origin columns for all views in Oracle database

Question

Let's assume I have following tables:

TABLE_A (ID; NAME; ...)
TABLE_B (ID; NAME; TABLE_A_FK; ...)

And I expose the table data through this view:

SELECT a.id, a.name, b.name FROM TABLE_A a
JOIN TABLE_B b
ON a.id = b.table_a_fk;

How could i get for my view the information which table and columns are referenced in this view.

Here I would expect:

TABLE_NAME  COLUMN_NAME
-----------------------
TABLE_A     ID
TABLE_A     NAME
TABLE_B     NAME
TABLE_B     TABLE_A_FK

Currently the only way to solve this is to parse the SQL text inside this statement:

select view_name, text_vc from sys.all_views;

But isn't funny at all ;).

Answer 1

This is something that seems to have been longed for for quite some time in the Oracle community and it seems to be possible since 11g, however it's not available pre-installed. One needs to look through an undocumented system table called dependency$, more specifically the d_attrs column.

Luckily there is an old community based fix to this called DBA_DEPENDENCY_COLUMNS, originally crafted by Rob van Wijk and made easier to copy by Ronald's Oracle.
Copy-pasting this view into your database (sys-privileges is needed) should give you the possibility to run some SQL like:

CREATE TABLE table_a(id NUMBER, name VARCHAR2(200), never_referenced_column_A VARCHAR2(100));
CREATE TABLE table_b(id NUMBER, name VARCHAR2(200), table_a_fk NUMBER, never_referenced_column_B NUMBER);

CREATE OR REPLACE VIEW TESTING_COLUMN_DEPENDENCIES AS 
SELECT a.id,
       a.name a_name,
       b.name b_name
  FROM table_a a
  JOIN table_b b
    ON a.id = b.table_a_fk;

SELECT NAME,
       referenced_name,
       referenced_column
  FROM sys.dba_dependency_columns
 WHERE referenced_name IN ('TABLE_A', 'TABLE_B');

The never referenced columns of table A and B, usefully named such, does not appear in the final result as was intended.

Answer 2

you can use this query to get the column and table name

select c.table_name, 
       c.column_name, 
       c.column_id
       from sys.all_tab_columns c
inner join sys.all_views v on c.owner = v.owner 
                              and c.table_name = v.view_name
                              and v.view_name IN ('EDW_OPS_DB_ADM_VW')
order by c.table_name, c.column_id;

--This one only gives the table names select owner as schema_name, name as view_name, referenced_owner as schema_name, referenced_name, referenced_type from sys.all_dependencies where type = 'VIEW' AND name IN ('EDW_OPS_DB_ADM_VW') and owner in ( 'SYSADM') order by name ;