The stored procedure
VIEW_DEPENDENCIES returns a list of the
dependencies of one or more views (base and derived views).
This procedure allows you to obtain programmatically the same information that the Administration Tool displays in the “Tree view” dialog of a derived view.
VIEW_DEPENDENCIES ( input_view_database_name : text , input_view_name : text )
input_view_database_name: name of the database whose views you want to obtain the dependencies of.
input_view_name: name of the view you want to obtain its dependencies from.
Both parameters are optional:
If both parameters are
null, the procedure returns the dependencies of all views in all databases.
input_view_nameis not, the procedure returns the dependencies of all views having this name, in all databases.
input_view_database_nameis not, the procedure returns the dependencies of all views in that database (note that if a view has a dependency with a view of another database, it is also shown).
Each row of the result of this procedure represents a dependency between a derived view (the field “view_name”) and another element (“dependency name”), which can be a derived view, base view or a data source.
The output schema has the following fields:
view_database_name: name of the database that the view on the field “view_name” belongs to.
view_name: name of the derived view.
view_identifier: identifier of the view. It is autogenerated by the stored procedure and may change between executions of the procedure. See the example below to understand how to use this value.
view_type: type of view. I.e. Base view, Select, Join, Interface, etc. When the type of the view is join, the value of this field includes the following information, in this order:
Type of join: it can be “Inner”, “Left outer”, “Right outer” or “Full outer”.
Join method: it can be “Any”, “Hash”, “Nested”, “Nested parallel” or “Merge”. “Any” means that the user did not select a method to execute the join when creating the view. Therefore, the Execution Engine will automatically select a method, unless one is selected in the “Execution plan” tab of the queried view.
Order of the join: it can be “Any”, “Ordered” or “Reverseorder”. “Any” means that the user did not select an order to execute the join when creating the view. Therefore, the Execution Engine will automatically select an order, unless one is selected in the “Execution plan” tab of the queried view.
trueif the view on the field “view_name” is private;
A view is private when “view_name” represents a join, minus, intersect view of another join/minus/intersect view and the view was internally by Virtual DataPort and not by the user.
For example, if you create a derived view called J_V that joins three views: (V1 JOIN V2) JOIN V3, internally Virtual DataPort creates a join view of V1 and V2. J_V will be the result of joining this internal view with V3. In this example, the join of V1 and V2 is a private view and J_V is public.
The information about private views allows you to know in which order a join/minus/intersect view of more than two views is performed.
dependency_database_name: name of the database that the dependency belongs to.
The value of this field will always be the same as “view_database_name”.
dependency_name: name of the element (view or data source) on which the view on the “view_name” field depends.
dependency_identifier: internal identifier of the element (view or data source) on which the view on the “view_name” field depends.
It is autogenerated by the stored procedure and may change between executions of the procedure.
dependency_type: type of element on which the view on the “view_name” field depends.
depth: distance between the view of the field “view_name” and the dependency.
When a row represents a direct dependency, the value of this field is
1. Direct dependencies are dependencies between views that directly depend on each other. E.g. a base view and a projection view created over this base view have a direct dependency.
When a row represents a transitive dependency, the value of this field is greater than
1. Transitive dependencies are dependencies between views that depend on each other, but there are other views in between. Let us say that we have created a selection view called P_V created over a base view BV1. In this example, there is a transitive dependency between P_V and the data source of the view BV1.
Let us say that we have created a view called
customer_invoicing like this:
(client_df LEFT OUTER JOIN invoice) INNER JOIN product
The following image is a screenshot of the result of executing
SELECT view_name, view_identifier, view_type, private_view, dependency_name, dependency_identifier, dependency_type, depth FROM VIEW_DEPENDENCIES() WHERE input_view_database_name = 'customer360' AND input_view_name = 'customer_invoicing';
For formatting purposes, some columns are not displayed.
To understand the result, take into account the following:
The rows #5 to #9 represent the dependencies of a private view (the field “private” is
truein these rows). This private view is the left outer join between client and invoice.
The rows #11 and #12 till the last one describe the dependencies of the view
product. Note that all of them have the same “view_identifier”. All the dependencies of a view have the same “view_identifier”.
The rows whose value of “dependency_identifier” is 9 represent the direct or transitive dependencies with the XML data source “invoicing”:
Row #2 represents the direct dependency between the base view “invoicing” and the XML data source. The dependency is direct because the value of the “depth” field is
Row #4, the transitive dependency between the view “invoice” and the XML data source. This dependency is also transitive because “depth” is
Row #9, the transitive dependency between the private view and the XML data source. The dependency is transitive because “depth” is greater than
The information returned by the procedure changes depending on the type of user that executes the procedure:
Administrators: the procedure returns information about all the dependencies.
Administrators of a database: the procedure returns information about all the views of that database. However, it does not return any information about the dependencies of views of other databases.
Users that have
METADATAprivileges over a database: the procedure returns information about the dependencies of all views of that database. However, it does not return dependencies between views and data sources.
Users that have
METADATAprivileges over some views of a database: the procedure returns information about the dependencies of the fields of the views on which the user has the
METADATAprivilege granted. If the user does not have
METADATAprivilege granted over the dependency, the value of the field “dependency_type” will be “No Privileges”. The procedure does not return the dependencies between views and data sources.
Obtaining the dependencies of the view “campaign_evolution_per_country”:
SELECT DISTINCT dependency_type ,dependency_database_name ,dependency_name FROM view_dependencies() WHERE input_view_database_name = 'partner_demo' AND input_view_name = 'campaign_evolution_per_country' AND private_view = false AND substr(dependency_name FROM 1 FOR 1) <> '_' -- The views whose name start with "_" are private views. ORDER BY dependency_type ,dependency_name;