Retrieve 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.

Prerequisites

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

Context and remarks

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.

To retrieve dependency information using SQL

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

Results

A list of IDs for the dependent views is returned.

Next

None.

Example

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;

Retrieve dependency information (SQL)

To retrieve dependency information using SQL

See also