List of Stored Procedures/Functions Mysql Command

问题:

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

回答1:

SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;

回答2:

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.

回答3:

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 

回答4:

A more specific way:

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

回答5:

As mentioned above,

show procedure status;

Will indeed show a list of procedures, but shows all of them, server-wide.

If you want to see just the ones in a single database, try this:

SHOW PROCEDURE STATUS WHERE Db = 'databasename';

回答6:

Alternative:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES

回答7:

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)

回答8:

use this:

SHOW PROCEDURE STATUS;

回答9:

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)), '%'));

回答10:

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';

回答11:

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

回答12:

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

回答13:

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.

回答14:

                           show procedure status;

using this command you can see the all procedures in databases

回答15:

Use the following query for all the procedures:

select * from sysobjects 
where type='p'
order by crdate desc