Drop all stored procedures in MySQL or using temporary stored procedures

Question

Is there a statement that can drop all stored procedures in MySQL? Alternatively (if the first one is not possible), is there such thing as temporary stored procedures in MySQL? Something similar to temporary tables?

Answer 1

I would have thought that this would do it, but I'm open to corrections:

(EDITED to incorporate a good point provided in the comments)

delete from mysql.proc WHERE db LIKE <yourDbName>;

(As pointed out by Balmipour in the comments below, it's a good idea to specify the database.)

I think it's valid to want to drop all procedures in a given database, otherwise in a long development cycle there's a danger of obsolete procedures and functions accumulating and muddying everything up.

Answer 2

Since DROP PROCEDURE and DROP FUNCTION does not allow sub selects, I thought it might be possible to perform the operation through another stored procedure, but alas, MySQL does not allow stored procedures to drop other stored procedures.

I tried to trick MySQL to to this anyway by creating prepared statements and thus separating the drop call somewhat from the stored procedure, but I've had no luck.

So therefore my only contribution is this select statement which creates a list of the statements needed to drop all stored procedures and functions.

SELECT
    CONCAT('DROP ',ROUTINE_TYPE,' `',ROUTINE_SCHEMA,'`.`',ROUTINE_NAME,'`;') as stmt
FROM information_schema.ROUTINES;