How to delete all procedures of a database in mysql5

Question

I want to delete all procedures from my MySQL database sbnmaster. How should I write a query for this?

http://stackoverflow.com/questions/3027832/drop-all-stored-procedures-in-mysql-or-using-temporary-stored-procedures — gmaliar, Aug 25 '11 at 09:26

score 9 · Accepted Answer · answered Aug 25 '11 at 09:36

9

Try this

USE sbnmaster;

SELECT CONCAT("DROP PROCEDURE IF EXISTS ",SPECIFIC_NAME) AS StorePrecedure 
FROM information_schema.ROUTINES R 
WHERE R.ROUTINE_TYPE = "PROCEDURE" 
AND R.ROUTINE_SCHEMA = DATABASE();

answered Aug 25 '11 at 09:36

Rahul

73,987
13
62
116

I had to add a semicolon to the concat to get it to work with several procs: `SELECT CONCAT("DROP PROCEDURE IF EXISTS ",SPECIFIC_NAME,";") AS StorePrecedure ` – BenR Feb 18 '14 at 20:24

score 7 · Answer 2 · answered Aug 25 '11 at 09:57

7

Not sure that it is correct, but seems it works -

DELETE FROM mysql.proc WHERE db = 'sbnmaster' AND type = 'PROCEDURE';

answered Aug 25 '11 at 09:57

Devart

115,199
22
161
180

How to delete all procedures of a database in mysql5

2 Answers2