Make truncate all tables in a database in MySQL

Question

Make truncate all tables in a database in MySQL

Navigation

#1 by (9 votes)

8

Is there a way to use TRUNCATE to clear all tables in the database?

TRUNCATE tabela;

This command clears one by one.

mysql sql

asked by anonymous 10.03.2015 / 19:38

1 answer

What does "::" mean in C ++? What is a Prototyping Language?

score 9 · Accepted Answer

Solution removed from this answer in the SO :

mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "truncate table $table" DATABASE_NAME; done

Or through SQL command :

SELECT Concat('TRUNCATE TABLE ', table_schema, '.', table_name, ';') 
    FROM INFORMATION_SCHEMA.TABLES where table_schema in ('seuDB'); //pode listar vários DBs

You can make several filters:

SELECT Concat('TRUNCATE TABLE ', table_schema, '.', table_name, ';') 
    FROM INFORMATION_SCHEMA.TABLES where table_schema in ('seuDB') AND table_name NOT IN ('tabela1', 'tabela2', ...);