CODEWITHSUNDEEP
mysqlmysql-workbench
esswar Saravanan
esswar Saravanan

Reputation: 13

How to drop all procedures and functions in MySQL

I want to drop all the procedures and functions in MySQL specific Database. I am trying to use this stored procedure for achieving this.

But I am getting an error:

Error Code: 1295 This command is not supported in the prepared statement protocol yet.

Thanks in advance.

delimiter $$
CREATE PROCEDURE sp_drop_all_stored_procedures()
begin
    declare flag boolean default false;
    declare sql_drop_statement varchar(1000);
  
    declare statements cursor for SELECT
    CONCAT('DROP ',ROUTINE_TYPE, ' ', ROUTINE_NAME, ';') as stmt
    FROM information_schema.ROUTINES where ROUTINE_SCHEMA = 'DB_NAME' and 
    ROUTINE_NAME != sp_drop_all_stored_procedures;
    
    declare continue handler for not found set flag := true;

    open statements;
    statements_loop: loop
        fetch statements into sql_drop_statement;
        
        if flag then
            leave statements_loop;
        end if;

        set @drop_statement = sql_drop_statement;
        select @drop_statement;
        PREPARE stmt FROM @drop_statement;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;  
        
    end loop statements_loop;
    close statements;
    
end $$
delimiter ;

Upvotes: 1

Views: 624

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562378

The list of SQL statements that can be executed as a prepared statement are documented here: https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html#prepared-statements-permitted (scroll down the page about two-thirds of the way).

DROP PROCEDURE and DROP FUNCTION are not among them.

But you wouldn't be able to do it even if you avoid using a prepared statement:

mysql> create procedure d() drop procedure p;
ERROR 1357 (HY000): Can't drop or alter a PROCEDURE from within another stored routine

Because of this, I don't think you can do this in a stored procedure. You will have to do it from a client, where you can format each SQL statement and execute it directly, not as a prepared statement.

Upvotes: 2

Related Questions