List of Stored Procedures/Functions Mysql Command

Question

How can I see the list of the stored procedures or stored functions in mysql command line like show tables; or show databases; commands.

Answer 1

Alternative:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES

Answer 2

SHOW PROCEDURE STATUS;

Shows all the stored procedures.

SHOW FUNCTION STATUS;

Shows all the functions.

SHOW CREATE PROCEDURE [PROC_NAME];

Will show the definition of the specified procedure.

SHOW PROCEDURE STATUS WHERE Db = '[db_name]';

Will show you all the procedures of the given database.

Answer 3

show procedure status

will show you the stored procedures.

show create procedure MY_PROC

will show you the definition of a procedure. And

help show

will show you all the available options for the show command.

Answer 4

SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;

Answer 5

My preference is for something that:

1. Lists both functions and procedures,
2. Lets me know which are which,
3. Gives the procedures' names and types and nothing else,
4. Filters results by the current database, not the current definer
5. Sorts the result

Stitching together from other answers in this thread, I end up with

select 
  name, type 
from 
  mysql.proc 
where 
  db = database() 
order by 
  type, name;

... which ends you up with results that look like this:

mysql> select name, type from mysql.proc where db = database() order by type, name;
+------------------------------+-----------+
| name                         | type      |
+------------------------------+-----------+
| get_oldest_to_scan           | FUNCTION  |
| get_language_prevalence      | PROCEDURE |
| get_top_repos_by_user        | PROCEDURE |
| get_user_language_prevalence | PROCEDURE |
+------------------------------+-----------+
4 rows in set (0.30 sec)

Answer 6

For view procedure in name wise

select name from mysql.proc 

below code used to list all the procedure and below code is give same result as show procedure status

select * from mysql.proc