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

A variation on Praveenkumar_V's post:

SELECT `name` FROM mysql.proc WHERE db = 'dbname' AND `type` = 'PROCEDURE';
SELECT `name` FROM mysql.proc WHERE db = 'dbname' AND `type` = 'FUNCTION';

..and this because I needed to save time after some housekeeping:

SELECT CONCAT(
     "GRANT EXECUTE ON PROCEDURE `"
    ,`name`
    ,"` TO username@'%'; -- "
    ,`comment`
)
FROM mysql.proc
WHERE db = 'dbname'
AND `type` = 'PROCEDURE';

SELECT CONCAT(
     "GRANT EXECUTE ON FUNCTION `"
    ,`name`
    ,"` TO username@'%'; -- "
    ,`comment`
)
FROM mysql.proc
WHERE db = 'dbname'
AND `type` = 'FUNCTION';

Answer 2

If you want to list Store Procedure for Current Selected Database,

SHOW PROCEDURE STATUS WHERE Db = DATABASE();

it will list Routines based on current selected Database

Answer 3

SELECT specific_name FROM `information_schema`.`ROUTINES` WHERE routine_schema='database_name'

Answer 4

To show just yours:

SELECT
  db, type, specific_name, param_list, returns
FROM
  mysql.proc
WHERE
  definer LIKE
  CONCAT('%', CONCAT((SUBSTRING_INDEX((SELECT user()), '@', 1)), '%'));

Answer 5

                           show procedure status;

using this command you can see the all procedures in databases

Answer 6

A more specific way:

SHOW PROCEDURE STATUS 
WHERE Db = DATABASE() AND Type = 'PROCEDURE'