How can I see the list of the stored procedures or stored functions in mysql command line like show tables;
or show databases;
commands.
This question is related to
mysql
stored-procedures
command-line
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';
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';
Use the following query for all the procedures:
select * from sysobjects
where type='p'
order by crdate desc
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";
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`
;
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]';
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';
use this:
SHOW PROCEDURE STATUS;
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
SELECT specific_name FROM `information_schema`.`ROUTINES` WHERE routine_schema='database_name'
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)), '%'));
show procedure status;
using this command you can see the all procedures in databases
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';
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.
Alternative:
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
My preference is for something that:
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)
A more specific way:
SHOW PROCEDURE STATUS
WHERE Db = DATABASE() AND Type = 'PROCEDURE'
Source: Stackoverflow.com