List of Stored Procedures/Functions Mysql Command Line
MysqlStored ProceduresCommand LineMysql Problem Overview
How can I see the list of the stored procedures or stored functions in mysql command line like show tables;
or show databases;
commands.
Mysql Solutions
Solution 1 - Mysql
SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;
Solution 2 - Mysql
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.
Solution 3 - Mysql
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
Solution 4 - Mysql
A more specific way:
SHOW PROCEDURE STATUS
WHERE Db = DATABASE() AND Type = 'PROCEDURE'
Solution 5 - Mysql
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';
Solution 6 - Mysql
Alternative:
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
Solution 7 - Mysql
My preference is for something that:
- Lists both functions and procedures,
- Lets me know which are which,
- Gives the procedures' names and types and nothing else,
- Filters results by the current database, not the current definer
- 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)
Solution 8 - Mysql
Shows all the stored procedures:
SHOW PROCEDURE STATUS;
Shows all the functions:
SHOW FUNCTION STATUS;
Shows the definition of the specified procedure:
SHOW CREATE PROCEDURE [PROC_NAME];
Shows you all the procedures of the given database:
SHOW PROCEDURE STATUS WHERE Db = '[db_name]';
Solution 9 - Mysql
use this:
SHOW PROCEDURE STATUS;
Solution 10 - Mysql
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';
Solution 11 - Mysql
SELECT specific_name FROM `information_schema`.`ROUTINES` WHERE routine_schema='database_name'
Solution 12 - Mysql
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)), '%'));
Solution 13 - Mysql
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
UPDATED to list out functions in your database
select * from information_schema.ROUTINES where ROUTINE_SCHEMA="YOUR DATABASE NAME" and ROUTINE_TYPE="FUNCTION";
to list out routines/store procedures in your database,
select * from information_schema.ROUTINES where ROUTINE_SCHEMA="YOUR DATABASE NAME" and ROUTINE_TYPE="PROCEDURE";
to list tables in your database,
select * from information_schema.TABLES WHERE TABLE_TYPE="BASE TABLE" AND TABLE_SCHEMA="YOUR DATABASE NAME";
to list views in your database,
method 1:
select * from information_schema.TABLES WHERE TABLE_TYPE="VIEW" AND TABLE_SCHEMA="YOUR DATABASE NAME";
method 2:
select * from information_schema.VIEWS WHERE TABLE_SCHEMA="YOUR DATABASE NAME";
Solution 14 - Mysql
As of MySQL 8.0, the mysql.procs
table has been removed. Running any of the commands from answers here that use this table will yield you an error that says (quite logically):
Table 'mysql.proc' doesn't exist
Instead, to retrieve a list of only the names of procedures/functions, use:
SELECT specific_name FROM `information_schema`.`ROUTINES` WHERE routine_schema='<your_db_name>';
In my case, I edited it to show only the procedures and not the functions:
SELECT specific_name FROM `information_schema`.`ROUTINES` WHERE routine_schema='<your_db_name>' AND routine_type='PROCEDURE';
Solution 15 - Mysql
My favorite rendering of the procedures list of the current database: name, parameters list, comment
SELECT specific_name AS name, param_list AS params, `comment`
FROM mysql.proc
WHERE db = DATABASE()
AND type = 'PROCEDURE';
Add returns for functions:
SELECT specific_name AS name, param_list AS params, `returns`, `comment`
FROM mysql.proc
WHERE db = DATABASE()
AND type = 'FUNCTION';
Solution 16 - Mysql
show procedure status;
using this command you can see the all procedures in databases
Solution 17 - Mysql
List user's procedures and functions for all databases:
SELECT
`ROUTINE_SCHEMA` AS `database`
,`ROUTINE_TYPE` AS `type`
,`SPECIFIC_NAME` AS `name`
,`DTD_IDENTIFIER` AS `data_type`
FROM
`INFORMATION_SCHEMA`.`ROUTINES`
WHERE
`definer` LIKE
CONCAT('%', CONCAT((SUBSTRING_INDEX((SELECT user()), '@', 1)), '%'))
ORDER BY
`database`
,`type`
,`name`
;
List user's procedures and functions for the database in use:
SELECT
`ROUTINE_SCHEMA` AS `database`
,`ROUTINE_TYPE` AS `type`
,`SPECIFIC_NAME` AS `name`
,`DTD_IDENTIFIER` AS `data_type`
FROM
`INFORMATION_SCHEMA`.`ROUTINES`
WHERE
`definer` LIKE
CONCAT('%', CONCAT((SUBSTRING_INDEX((SELECT user()), '@', 1)), '%'))
AND
`ROUTINE_SCHEMA` = DATABASE()
ORDER BY
`type`
,`name`
;
Solution 18 - Mysql
Use the following query for all the procedures:
select * from sysobjects
where type='p'
order by crdate desc