CODEWITHSUNDEEP
sqloracle-databasemetadatadatabase-administration
hermione
hermione

Reputation: 35

find the source table of each column in view

I'm Trying to write a query that extracts the tables in a view and map each column from that view to the corresponding table. All I was able to do is get the table names only, I'm facing the options of either parsing the ddl or perhaps create a table to document the metadata, but both are very costly for the task at hand. I've also tries using DBMS-METADATA but I cannot find a way to make it work properly since I'm kinda new to DBA. Is there any way i can find the source of the columns? thanks in advance!

Upvotes: 2

Views: 2562

Answers (1)

krokodilko
krokodilko

Reputation: 36087

If it is one-to-one mapping, and if names of all columns in tables/views refereced by "main" view are unique (all referenced tables and views cannot have any column with the same name),
then you can query USER_DEPENDENCIES and USER_TAB_COLUMNS, see a demo:

CREATE TABLE A( a int, b int, c int );

CREATE TABLE x( x int, y int, z int );

CREATE VIEW ax as 
select a as aa, b as bb, x as xx, y as yy
from a join x on a = x
;

CREATE VIEW my_view as
select a.a, x.z, ax.*
from a
cross join x
cross join ax
;

select c1.column_name, ' comes from ' as "Comes from", d.referenced_name 
from  user_dependencies d
join user_tab_columns cc ON cc.table_name = d.referenced_name
join user_tab_columns c1 ON c1.table_name = d.name AND c1.column_name = cc.column_name
where d.name = 'MY_VIEW';

COLUMN_NAME Comes from   REFERENCED_NAME   
----------- ------------ ---------------
A           comes from   A          
Z           comes from   X          
YY          comes from   AX         
XX          comes from   AX         
BB          comes from   AX         
AA          comes from   AX

But if any two tables (or views) have a column with the same name, then this query gives wrong results - in this case the only option is to parse a view definition.

Upvotes: 2

Related Questions