Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

4 Answers 4

up vote 11 down vote accepted
SHOW PROCEDURE STATUS
SHOW FUNCTION STATUS

See related StackOverflow question.

share|improve this answer

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"
;
share|improve this answer
1  
This also works in Oracle and SQL Server. I think its part of an ANSI standard –  Conrad Frix Jan 13 '11 at 5:41

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 ;
share|improve this answer
    
This is a bit overcomplicated, see Gaius's answer. –  dezso Nov 2 '12 at 22:17

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');
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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