CODEWITHSUNDEEP
mysqldatabasestored-procedurescommand-linemysql-function
systemsfault
systemsfault

Reputation: 15527

Show Procedures/Functions MySQL Command Line

How can I see the list of the stored procedures or stored functions in mysql command line like SHOW TABLES; or SHOW DATABASES; commands.

Upvotes: 524

Views: 642867

Answers (20)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

You can show all functions with the SQL below. *The doc explains SHOW FUNCTION STATUS and I recommend to use \G which can show them more clearly and my answer explains how to show procedures in MySQL:

SHOW FUNCTION STATUS;

Or:

SHOW FUNCTION STATUS\G

And, you can show the functions of apple database with the SQL below:

SHOW FUNCTION STATUS WHERE Db = 'apple';

Or:

SHOW FUNCTION STATUS WHERE Db = 'apple'\G

And, you can show all functions with the SQL below. *The doc explains INFORMATION_SCHEMA.ROUTINES table:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION';

Or:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION'\G

And, you can show the functions of apple database with the SQL below:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION' AND ROUTINE_SCHEMA = 'apple';

Or:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION' AND ROUTINE_SCHEMA = 'apple'\G

Upvotes: 0

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

You can show all procedures with the SQL below. *The doc explains SHOW PROCEDURE STATUS and I recommend to use \G which can show them more clearly and my answer explains how to show functions in MySQL:

SHOW PROCEDURE STATUS;

Or:

SHOW PROCEDURE STATUS\G

And, you can show the procedures of apple database with the SQL below:

SHOW PROCEDURE STATUS WHERE Db = 'apple';

Or:

SHOW PROCEDURE STATUS WHERE Db = 'apple'\G

And, you can show all procedures with the SQL below. *The doc explains INFORMATION_SCHEMA.ROUTINES table:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';

Or:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'\G

And, you can show the functions of apple database with the SQL below:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = 'apple';

Or:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = 'apple'\G

Upvotes: 0

Alistair Jones
Alistair Jones

Reputation: 583

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

Upvotes: 6

Jimmix
Jimmix

Reputation: 6506

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

Upvotes: 0

dolmen
dolmen

Reputation: 8706

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

Upvotes: 3

Optimizer
Optimizer

Reputation: 727

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

Upvotes: 18

Mohideen bin Mohammed
Mohideen bin Mohammed

Reputation: 20147

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

Upvotes: 8

trapper_hag
trapper_hag

Reputation: 780

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

Upvotes: 9

John Haugeland
John Haugeland

Reputation: 9678

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)

Upvotes: 25

mwesigwa
mwesigwa

Reputation: 19

Use the following query for all the procedures:

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

Upvotes: -2

Sunil Kumar
Sunil Kumar

Reputation: 665

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

Upvotes: 8

Karan
Karan

Reputation: 587

                           show procedure status;

using this command you can see the all procedures in databases

Upvotes: 1

sharninder
sharninder

Reputation:

use this:

SHOW PROCEDURE STATUS;

Upvotes: 15

Charlie Skilbeck
Charlie Skilbeck

Reputation: 1141

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

Upvotes: 8

fredrik
fredrik

Reputation: 13550

SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;

Upvotes: 797

macio.Jun
macio.Jun

Reputation: 9895

Alternative:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES

Upvotes: 34

Praveenkumar_V
Praveenkumar_V

Reputation: 1394

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

Upvotes: 106

5422m4n
5422m4n

Reputation: 950

A more specific way:

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

Upvotes: 57

Drarok
Drarok

Reputation: 3809

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

Upvotes: 39

unbeknown
unbeknown

Reputation: 

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.

Upvotes: 226

Related Questions