MySQL 8 - Remove All Stored Procedures in a Specific Database

user_10028342 0
Viewed 1657

In MySQL 8 the database table `mysql`.`proc` does not exist. Previously I used this table to delete/remove/clear all stored procedures and recreate them from the source versioned code. This works very well to make sure development stored procedures where saved to source versioning before going to production.

In MySQL versions before 8, this SQL query worked.

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

Is there an alternate way to achieve the results of the query in one statement?

The answer is you can't. But you can generate all the drop statements and execute them.

I now create all of the drop statements for stored procedures, stored functions, events, and triggers all at once using the following SQL. I use the function "DATABASE()" for ease of reuse. You can replace "DATABASE()" with the database name string "dbName" and it will work in phpMyAdmin.

SELECT CONCAT("DROP ",`item`.`ROUTINE_TYPE`," IF EXISTS `",DATABASE(),"`.`",`item`.`ROUTINE_NAME`,"`;") as `stmt`
FROM `information_schema`.`ROUTINES` AS `item` 
WHERE `item`.`ROUTINE_SCHEMA` = DATABASE()
    UNION
SELECT CONCAT("DROP EVENT IF EXISTS `",DATABASE(),"`.`",`item`.`EVENT_NAME`,"`;") AS `stmt`
FROM `information_schema`.`EVENTS` AS `item` 
WHERE `item`.`EVENT_SCHEMA` = DATABASE()
    UNION
SELECT CONCAT("DROP TRIGGER IF EXISTS `",DATABASE(),"`.`",`item`.`TRIGGER_NAME`,"`;") AS `stmt`
FROM `information_schema`.`TRIGGERS` AS `item` 
WHERE `item`.`TRIGGER_SCHEMA` = DATABASE();
sql-delete stored-procedures mysql
4 Answers

In MySQL 8.0, details of the Stored procedure (Routines) are accessible via the Routines table of the INFORMATION_SCHEMA. Following points are noteworthy:

The ROUTINES table provides information about stored routines (stored procedures and stored functions). The ROUTINES table does not include built-in SQL functions or user-defined functions (UDFs).

Now, the following two columns in the Routines table are required to use here:

ROUTINE_SCHEMA - The name of the schema (database) to which the routine belongs.

ROUTINE_TYPE - PROCEDURE for stored procedures, FUNCTION for stored functions.

On further investigation using SHOW CREATE TABLE information_schema.routines, it is found to be a TEMPORARY TABLE

So, in order to delete all the stored procedure(s) (and not stored function(s)) of a test database, we can use the following query:

DELETE FROM information_schema.routines 
WHERE routine_type = 'PROCEDURE' AND 
      routine_schema = 'test'

user_24693080

I suggest using MySQL Shell - the following example use the Python mode:

mysqlsh> \py

mysql-py> i_s = session.get_schema("information_schema")

mysql-py> functions = i_s.ROUTINES \
       ->                .select("sys.quote_identifier(ROUTINE_SCHEMA) AS RoutineSchema", "sys.quote_identifier(ROUTINE_NAME) AS RoutineName") \
       ->                .where("ROUTINE_SCHEMA = 'db1' AND ROUTINE_TYPE = 'FUNCTION'").execute().fetch_all()

mysql-py> functions
[
    [
        "`db1`",
        "`func1`"
    ], 
    [
        "`db1`",
        "`func2`"
    ], 
    [
        "`db1`",
        "`func``3`"
    ]
]

mysql-py> for function in functions:
       ->     print(sql_fmt.format(*function))
       ->     session.sql(sql_fmt.format(*function)).execute()
       ->
DROP FUNCTION `db1`.`func1`
DROP FUNCTION `db1`.`func2`
DROP FUNCTION `db1`.`func``3`
Query OK, 0 rows affected (0.0684 sec)

For more discussion see also https://mysql.wisborg.dk/2018/12/02/mysql-8-drop-several-stored-events-procedures-or-functions/ .

user_107361640

I think the answer to your question is that this cannot be done in one statement.

Maybe it would be helpful to use the following statements to generate the list of DROP statements:

SET group_concat_max_len = 4294967295;

SELECT group_concat('DROP PROCEDURE IF EXISTS ', r.routine_name, ';\n' ORDER BY r.routine_name SEPARATOR '') AS DROP_STATEMENTS 
FROM information_schema.routines r 
WHERE r.routine_schema = database() AND r.routine_type = 'PROCEDURE';
user_17873630

try 

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