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
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
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!