CODEWITHSUNDEEP
mysqlstored-procedures
Kenston Choi
Kenston Choi

Reputation: 2942

Drop all stored procedures in MySQL or using temporary stored procedures

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?

Upvotes: 24

Views: 36212

Answers (7)

Abdul Saleem
Abdul Saleem

Reputation: 10622

I use select statement to display all Drop Procedure statements of a specific database in one cell. Then copy it to query window.

SET group_concat_max_len = 4096;

SELECT GROUP_CONCAT(Procedures SEPARATOR '; ') From (SELECT CONCAT(
"DROP PROCEDURE IF EXISTS `",SPECIFIC_NAME, '`') AS Procedures 
FROM information_schema.ROUTINES R WHERE R.ROUTINE_TYPE = "PROCEDURE" 
AND R.ROUTINE_SCHEMA = 'my_database_name') As CopyThis;

Upvotes: 2

user1070300
user1070300

Reputation: 896

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.

Upvotes: 30

Balmipour
Balmipour

Reputation: 3055

@user1070300's answer seems to work, but it looks like it can drop a lot of things.

A quick DESC mysql.proc; led me to add a WHERE to my request, so as to spare already existing MySQL procedures, which I had, of course, no reason to drop.

I executed DELETE FROM mysql.proc WHERE db NOT LIKE 'mysql';
If you have several bases and want to target only one, use DELETE FROM mysql.proc WHERE db LIKE '<yourDbName>' instead;

Btw, if like me, you are completely clearing your database and need to also drop your tables, you can use this linux shell script : mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "drop table $table" DATABASE_NAME; done (see Truncate all tables in a MySQL database in one command? for more details)

Truncate all tables in a MySQL database in one command?

Upvotes: 0

Ivar Bonsaksen
Ivar Bonsaksen

Reputation: 4767

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;

Upvotes: 25

Levitron
Levitron

Reputation: 1033

This seems to drop the procedures...not sure about implications though

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

Upvotes: 11

Angelin Nadar
Angelin Nadar

Reputation: 9300

after the select statement which creates a list of the statements needed to drop all stored procedures and functions. You can avoid the manual work of copy pasting the listed queries as follows:

mysql>SELECT
    CONCAT('DROP ',ROUTINE_TYPE,' `',ROUTINE_SCHEMA,'`.`',ROUTINE_NAME,'`;') as stmt
FROM information_schema.ROUTINES into outfile '/tmp/a.txt';

mysql> source /tmp/a.txt;

Upvotes: 1

I answer wrong - have fun
I answer wrong - have fun

Reputation: 2831

I can almost get working a piece of code to drop all stored procs, but I think MySQL isn't letting me use a LOOP or a CURSOR outside of a stored procedure.

I can write a SQL file that counts the number of stored procedures for a given schema, and I have the code to iterate through the table and drop procedures, but I can't get it to run:

SELECT COUNT(ROUTINE_NAME)
  INTO @remaining
  FROM information_schema.ROUTINES
  WHERE ROUTINE_SCHEMA = SCHEMA()
    AND ROUTINE_TYPE = 'FUNCTION';

kill_loop: LOOP
  IF @remaining < 1 THEN
    LEAVE kill_loop;
  END IF;
  SELECT ROUTINE_NAME
    INTO @cur_func_name
    FROM information_schema.ROUTINES
    WHERE ROUTINE_SCHEMA = SCHEMA()
      AND ROUTINE_TYPE = 'FUNCTION'
    LIMIT 1;
  DROP FUNCTION @cur_func_name;
  @remaining = @remaining - 1;
END LOOP;

Upvotes: 3

Related Questions