get_event_dependencies
NAME
get_event_dependencies(): Analyze and list the dependencies of a given event (BETA)TYPE
ProcedureDESCRIPTION
This procedure will analyze the CREATE EVENT statement of the given event, and provide with dependency listing: the objects on which this event depends, e.g. tables or routines.
get_event_dependencies() will parse the internal event's stored routine code, detect queries issued within, including calls to other routines, and will list such dependencies.
The routine does not perform deep search, and will not analyze views or routines on which the given event depends.
It is not, and will not be, able to parse dynamic SQL, i.e. prepared statements made from string literals.
This procedure calls upon the more generic get_sql_dependencies() routine.
This code is in BETA stage.
SYNOPSIS
get_event_dependencies (
IN p_routine_schema VARCHAR(64) CHARSET utf8
, IN p_routine_name VARCHAR(64) CHARSET utf8
)
DETERMINISTIC
READS SQL DATA
Input:
- p_table_schema: schema where event is located.
- p_table_name: name of event.
STRUCTURE
The procedure returns a result set of dependencies for this event, 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', 'call' etc.).
EXAMPLES
Analyze an event on sakila:
mysql> CREATE EVENT
sakila.purge_history
ON SCHEDULE
EVERY 1 DAY
ON COMPLETION PRESERVE
ENABLE
DO
DELETE FROM sakila.rental WHERE rental_date < DATE(NOW() - INTERVAL 5 YEAR);
mysql> call common_schema.get_event_dependencies('sakila', 'purge_history');
+-------------+-------------+-------------+--------+
| schema_name | object_name | object_type | action |
+-------------+-------------+-------------+--------+
| sakila | rental | table | delete |
+-------------+-------------+-------------+--------+