Suppose we have a requirement to generate the report of fetch
the data from the database that shows the information about the view like
schema where the view is created, referenced owner, table etc.
Query:-
select owner as View_Schema,
name as View_Name,
referenced_owner as Referenced_Schema,
referenced_name as Referenced_Name,
Referenced_Type
from
sys.dba_dependencies
where type='VIEW'
and referenced_type in ('TABLE','VIEW')
and
owner = 'Enter the name of the schema'
and name = 'Enter the name of the view'
order by owner, view_name;
O/P:-
View_Schema
|
View_Name
|
Referenced_Schema
|
Referenced_Name
|
Referenced_Type
|
EMP
|
Vw_EmployeeDetails
|
HR
|
EmployeeDetails
|
Table
|
View_Schema -> Where the view is created
View_Name -> Name of the view
Referenced_Schema-> Referenced schema where the table is available
i.e used in the view.
Referenced_Type -> Type
of reference like table
What other ideas can you add to this post
that I may have not mentioned?
