MySQL Forums
Forum List  »  Stored Procedures

Re: stored procedure to list stored procedures
Posted by: William Chiquito
Date: February 01, 2007 02:47PM

Hi aufmy,

Try:
DELIMITER $$

DROP PROCEDURE IF EXISTS `list_stored_routines`$$

CREATE PROCEDURE `list_stored_routines`(show_body INT)
	READS SQL DATA
BEGIN
	IF (show_body = 1) THEN
		SELECT routine_name, routine_type, definer, routine_definition FROM information_schema.routines 
                 WHERE routine_schema = DATABASE();
	ELSE
                SELECT routine_name, routine_type, definer FROM information_schema.routines 
                 WHERE routine_schema = DATABASE();
	END IF;
END$$

DELIMITER ;

OR

DELIMITER $$

DROP PROCEDURE IF EXISTS `list_stored_routines`$$

CREATE PROCEDURE `list_stored_routines`(show_body INT)
	READS SQL DATA
BEGIN
	DECLARE myDB VARCHAR(64) DEFAULT DATABASE();
	IF (show_body = 1) THEN
		SET @qry = CONCAT('SELECT routine_name, routine_type, definer, routine_definition FROM information_schema.routines 
                           WHERE routine_schema = ''', myDB, '''');
	ELSE
		SET @qry = CONCAT('SELECT routine_name, routine_type, definer FROM information_schema.routines 
                           WHERE routine_schema = ''', myDB, '''');
	END IF;
	PREPARE stmt FROM @qry;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

See:




Edited 5 time(s). Last edit at 02/01/2007 03:23PM by William Chiquito.

Options: ReplyQuote


Subject
Views
Written By
Posted
26056
February 01, 2007 01:13PM
Re: stored procedure to list stored procedures
5900
February 01, 2007 02:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.