17

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

19
SHOW PROCEDURE STATUS
SHOW FUNCTION STATUS

See related StackOverflow question.

26

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"
;
  • 4
    This also works in Oracle and SQL Server. I think its part of an ANSI standard – Conrad Frix Jan 13 '11 at 5: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
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 ;
  • 1
    This is a bit overcomplicated, see Gaius's answer. – dezso Nov 2 '12 at 22:17
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');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.