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 10 · Accepted Answer · edited May 06 '23 at 21:22

10

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

edited May 06 '23 at 21:22

StevenWhite

5,907
3
21
46

answered Aug 25 '11 at 09:36

Rahul

76,197
13
71
125

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

119,203
23
166
186

How to delete all procedures of a database in mysql5

2 Answers2