List of Stored Procedures Functions Mysql Command Line

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

User · Answer

use this   SHOW PROCEDURE STATUS

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SHOW PROCEDURE STATUS  SHOW FUNCTION STATUS

User · Answer

A more specific way   SHOW PROCEDURE STATUS  WHERE Db   DATABASE   AND Type    PROCEDURE

User · Answer

Alternative   SELECT   FROM INFORMATION SCHEMA ROUTINES

User · Answer

show procedure status    using this command you can see the all procedures in databases

User · Answer

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 gt  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

User · Answer

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

User · Answer

SELECT specific name FROM  information schema   ROUTINES  WHERE routine schema  database name

User · Answer

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

User · Answer

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

User · Answer

Use the following query for all the procedures   select   from sysobjects  where type  p  order by crdate desc

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   lt your db name gt     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   lt your db name gt   AND routine type  PROCEDURE

[mysql] List of Stored Procedures/Functions Mysql Command Line

Examples related to mysql

Examples related to stored-procedures

Examples related to command-line