get_routine_dependencies
NAME
get_routine_dependencies(): Analyze and list the dependencies of a given routine (BETA)TYPE
ProcedureDESCRIPTION
This procedure will analyze the CREATE PROCEDURE or CREATE FUNCTION statement of the given routine, and provide with dependency listing: the objects on which this routine depends, e.g. tables or routines.
get_routine_dependencies() will parse the internal 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 routine 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_routine_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 routine is located.
- p_table_name: name of routine.
STRUCTURE
The procedure returns a result set of dependencies for this routine, 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 sakila's inventory_in_stock routine:
mysql> call get_routine_dependencies('sakila', 'inventory_in_stock');
+-------------+-------------+-------------+--------+
| schema_name | object_name | object_type | action |
+-------------+-------------+-------------+--------+
| sakila | inventory | table | select |
| sakila | rental | table | select |
+-------------+-------------+-------------+--------+