common schema. Listing Functions and stored procedures with its dependencies

Posted by in MariaDB, MySQL

Let’s keep on learning more about common_schema. In today’s entrance, let’s introduce two more functionalities, very cool for developers, because they are related with funcions and stored procedures.

Let’s begin with stored procedures (SP). If you need a list of all stored procedures of our server, just type this SQL sentence:

SHOW PROCEDURE STATUS;
However, this sentence is not as useful as it should for a developer. Let’s try a new one, and see if it’s better:

SELECT routine_schema, specific_name, routine_name, routine_body, routine_comment FROM information_schema.routines
WHERE routine_type = "PROCEDURE";

Podemos incluir en la cláusula WHERE limitar a una base de datos concreta.

stored procedures

stored procedures

Now, let’s use the procedure get_routine_dependencies (schema, procedure) to see the dependences of one concrete stored procedure!

CALL get_routine_dependencies (schema, procedimento);

And, this way you can know what dependencies a stored procedure has!

get_routine_dependencies

get_routine_dependencies

Now, let’s modify the SQL sentence to apply exactly the same, but for functions:
SHOW FUNCTION STATUS;
SELECT routine_schema, specific_name, routine_name, routine_body, routine_comment FROM information_schema.routines
WHERE routine_type = "FUNCTION";

And for the dependencies, the SQL sintax is exactly the same:
CALL get_routine_dependencies (schema, function);

In my opinion, these two functions are very useful for developers, specially if you need to refresh info about a SP of a function that you don’t update for a long time!

I hope if can be useful for you!