get_view_dependencies

NAME

get_view_dependencies(): Analyze and list the dependencies of a given view (BETA)

TYPE

Procedure

DESCRIPTION

This procedure will analyze the CREATE VIEW statement of the given view, and provide with dependency listing: the objects on which this view depends, e.g. tables or routines.

The routine does not perform deep search, and will not analyze views or routines on which the given view depends.

This procedure calls upon the more generic get_sql_dependencies() routine.

This code is in BETA stage.

SYNOPSIS

get_view_dependencies (
    IN p_table_schema VARCHAR(64) CHARSET utf8
,   IN p_table_name VARCHAR(64) CHARSET utf8
)
DETERMINISTIC
READS SQL DATA

Input:

  • p_table_schema: schema where view is located.
  • p_table_name: name of view.

STRUCTURE

The procedure returns a result set of dependencies for this view, in same format as in get_sql_dependencies():

  • schema_name: schema where dependency is located.
  • object_name: name of dependency object.
  • object_type: type of dependency object (e.g. 'table', 'function' etc.).
  • action: type of action performed on object (e.g. 'select', 'create' etc.).

EXAMPLES

Analyze sakila's actor_info view, which joins several tables:

mysql> call get_view_dependencies('sakila', 'actor_info');
+-------------+---------------+-------------+--------+
| schema_name | object_name   | object_type | action |
+-------------+---------------+-------------+--------+
| sakila      | actor         | table       | select |
| sakila      | category      | table       | select |
| sakila      | film          | table       | select |
| sakila      | film_actor    | table       | select |
| sakila      | film_category | table       | select |
+-------------+---------------+-------------+--------+

ENVIRONMENT

MySQL 5.1 or newer

SEE ALSO

get_event_dependencies(), get_routine_dependencies(), get_sql_dependencies()

AUTHOR

Roland Bouman
 
common_schema documentation