Retrieving dependency information (SQL)

For any table or view in the database, you can retrieve a list of objects that are dependent on that object. This is useful when you want to alter a table or view and need to know the other objects that will be impacted.

Prérequis

Execution of the task does not require any privileges and assumes that PUBLIC has access to the catalog.

Contexte et remarques

The SYSDEPENDENCY system view stores dependency information. Each row in the SYSDEPENDENCY system view describes a dependency between two database objects. A direct dependency is when one object directly references another object in its definition. The database server uses direct dependency information to determine indirect dependencies as well. For example, suppose View A references View B, which in turn references Table C. In this case, View A is directly dependent on View B, and indirectly dependent on Table C.

Task

Connect to the database.
Execute a statement that calls the sa_dependent_views system procedure.

Résultat

A list of IDs for the dependent views is returned.

Exemple

In this example, the sa_dependent_views system procedure is used in a SELECT statement to obtain the list of names of views dependent on the SalesOrders table. The procedure returns the ViewSalesOrders view.

SELECT t.table_name FROM SYSTAB t,  
sa_dependent_views( 'SalesOrders' ) v 
WHERE t.table_id = v.dep_view_id;

Retrieving dependency information (SQL)

Task

See also