In our development environment, we want to have a DeleteData.sql query that developers can use to delete all of the data of all tables of all databases of their MariaDB docker instance.

For example, let's say our developers work on a project that has 3 databases:

  • Accounts
  • Products
  • Orders

And let's say each database has 10 tables. We want to run DeleteData.sql and delete data from all tables in these three databases.

We do this to give developers a tool to test different data sets both for performance testing and for data validation and consistency.

But we have no idea how we can achieve this. In SQL Server we would:

  1. Use a cursor to loop over databases
  2. Create delete statements for all tables of each database dynamically
  3. Use execute sp_executesql to run that query dynamically on that database

Is it possible in MariaDB? Is there a better way to get to this result?

  • 1
    As far as in mysql/mariadb cursors are available only within stored procedures there is no difference between 1 and 3. I would use that approach - CALL truncate_tables( someuser ) - to truncate all the tables.
    – Kondybas
    Jul 2, 2022 at 12:43
  • I'm not sure MariaDB has sp_executesql, did you mean to tag your database system as such? If so, maybe you meant EXECUTE IMMEDIATE?
    – J.D.
    Jul 2, 2022 at 14:50
  • @J.D., that's for SQL Server. The problem with execute_immediate is that it seems that it only works inside a stored procedure. Jul 2, 2022 at 14:52

1 Answer 1


Plan A:

Write a script to create the 30 TRUNCATE statements from information_schema.TABLES and put them in the script.

(Note: TRUNCATE is faster than DELETE and also it resets AUTO_INCREMENT, so it is better at going back to the start.)

Plan B:

Use mysqldump --no--data to generate the tables and, I think, all the DROPs needed. (If not, preface with three DROP DATABASEs.)

  • Plan A is not dynamic. It can't be used for many applications with different sets of databases and tables. Thus that's not applicable. Plan B is interesting. In fact, Plan B is about deleting all databases first and then recreating their structure without data. Jul 3, 2022 at 3:23
  • @SaeedNeamati - DROP + reCREATE is the fastest way. (I think it is what TRUNCATE does. Plan A can be dynamic -- if you construct the statements, then PREPARE and EXECUTE them.
    – Rick James
    Jul 3, 2022 at 3:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.