get_sql_dependencies
NAME
get_sql_dependencies(): Analyze and list the dependencies of a given query (BETA)TYPE
ProcedureDESCRIPTION
This procedure will analyze the given query, and provide with dependency listing: the objects on which this query depends, e.g. tables, routines, views etc.
get_sql_dependencies() will parse the query's text to detect such objects. It will not validate their existence or correctness. It will not perform deep search in order to further find dependencies of those objects.
Thus, this routines does not actually perform any SQL operations, other than create and use internal temporary structures. It will not access INFORMATION_SCHEMA nor any other metadata.
It is not, and will not be, able to parse dynamic SQL, i.e. prepared statements made from string literals.
This procedure serves as the basis to other analysis routines.
This code is in BETA stage.
SYNOPSIS
get_sql_dependencies(
IN p_sql TEXT charset utf8
, IN p_default_schema VARCHAR(64) charset utf8
)
DETERMINISTIC
Input:
- p_sql: query to analyze
- p_default_schema: schema context to assume for query
STRUCTURE
The procedure returns a result set of dependencies for this routine:
- 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 a CREATE VIEW query:
mysql> call get_sql_dependencies('CREATE VIEW sakila.simple_actor AS SELECT actor_id, first_name FROM sakila.actor', 'sakila');
+-------------+--------------+-------------+--------+
| schema_name | object_name | object_type | action |
+-------------+--------------+-------------+--------+
| sakila | actor | table | select |
| sakila | simple_actor | view | create |
+-------------+--------------+-------------+--------+