How do I find out if a procedure or function exists in a mysql database?

Question

How do I find out if a procedure or function exists in a mysql database? and is there any discovery option? like a show procedures; (e.g. like show tables;)

score 30 · Answer 1 · edited Mar 06 '12 at 19:57

30

A generic answer to this type of question is that all MySQL databases include a database called information_schema which includes all the metadata as tables you can just query.

The information you want is in a table called ROUTINES. For example:

SELECT ROUTINE_NAME 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE 
       ROUTINE_TYPE="PROCEDURE" 
   AND ROUTINE_SCHEMA="dbname"
;

edited Mar 06 '12 at 19:57

Nick Chammas

14,670
17
75
121

answered Jan 12 '11 at 11:33

Gaius

11,200
3
31
64

4

This also works in Oracle and SQL Server. I think its part of an ANSI standard – Conrad Frix Jan 13 '11 at 05:41
1

Be aware that this can be affected by permissions -- you will see the list of procedures that you have access to see. This threw me off briefly when I got a partial list on one connection (using a more limited user name) and a different list on another connection. – Geoffrey Wiseman May 30 '17 at 16:07

score 22 · Accepted Answer · edited May 23 '17 at 11:33

22

SHOW PROCEDURE STATUS
SHOW FUNCTION STATUS

See related StackOverflow question.

edited May 23 '17 at 11:33

Community

1

answered Jan 12 '11 at 06:36

BenV

4,893
7
39
38

score 1 · Answer 3 · answered Mar 06 '12 at 19:17

1

use the following function:

DELIMITER $$

DROP FUNCTION IF EXISTS f_exists_procedure;$$
CREATE FUNCTION f_exists_procedure(in_name VARCHAR(255))
RETURNS BIT DETERMINISTIC
BEGIN
    SELECT COUNT(1) INTO @f_result
    FROM information_schema.ROUTINES as info
    WHERE info.ROUTINE_SCHEMA = DATABASE() AND info.ROUTINE_TYPE = 'PROCEDURE' AND info.ROUTINE_NAME = in_name;

    RETURN @f_result;

END;$$

DELIMITER ;

answered Mar 06 '12 at 19:17

5422m4n

111
1

1

This is a bit overcomplicated, see Gaius's answer. – András Váczi Nov 02 '12 at 22:17

score 0 · Answer 4 · answered Apr 17 '21 at 18:21

0

Another latest way:

    SHOW CREATE PROCEDURE <procedure_name> ;

answered Apr 17 '21 at 18:21

channa

350
7
21

score 0 · Answer 5 · edited Oct 06 '21 at 12:07

0

If you just want one specific procedure:

SHOW PROCEDURE STATUS WHERE `name` = 'value'

edited Oct 06 '21 at 12:07

mustaccio

25,896
22
57
72

answered Oct 06 '21 at 10:01

Chris Baaijens

1
1

user2242225 · Answer 6 · 2013-06-24T14:40:34.087

0

Spin Off of the answer from Gaius

SELECT IF( COUNT(*) = 0, 'F' , 'T' ) AS ProcedureExists
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_SCHEMA = 'someDBName'
AND ROUTINE_TYPE = 'PROCEDURE'
AND UCASE(ROUTINE_NAME) = UCASE('someProcedureName');

edited Jun 24 '13 at 14:40

answered Jun 24 '13 at 14:21

user2242225

1
1

How do I find out if a procedure or function exists in a mysql database?

6 Answers6