How can I truncate all tables from a MySQL Database?

Question

Is there any way to truncate all tables from a specific MySQL database name without using any other language than SQL? I mean no linux shell scripts. (why? because it will run on windows, MacOSX and linux servers).

the problem is that the client its selecting the database name from a list in a control panel webpage (wich will be displaying MySQL databases from different servers *nix and windows), and then he will want to truncate all the tables inside that database (yes that is the main task of the web form).

Alex

score 2 · Accepted Answer · edited Oct 09 '16 at 16:31

Ok, I solved it by myself here is the stored procedure :)

BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE; 
    DECLARE truncatestmnt TEXT; -- this is where the truncate statement will be retrieved from cursor

    -- This is the magic query that will bring all the table names from the database
    DECLARE c1 CURSOR FOR SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = "@DatabaseName";
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE; 

    OPEN c1;

    c1_loop: LOOP
    FETCH c1 INTO truncatestmnt;
    IF `done` THEN LEAVE c1_loop; END IF;
        SET @x = truncatestmnt;
        PREPARE stm1 FROM @x;
        EXECUTE stm1;
    END LOOP c1_loop; 

    CLOSE c1;
END

What I am making its calling all tables from the given database, this will help if the tables inside the given database have no pattern to follow.

So by calling DECLARE c1 CURSOR FOR SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = "@DatabaseName"; and saving results into a cursor I can fetch all the TRUNCATE TABLE x statements generated by the "n" quantity of tables inside the given database, then by just preparing and executing each statement in the cursor it will truncate all the tables inside the given database.

BTW @DatabaseName must be given as parameter to the stored procedure

Hope this helps someone else too :)

Alex

A problem that I see is, that it won't handle foreign key relationships. So it will fail when trying to truncate a table that is referenced by another table. — , Jan 21 '11 at 08:36
The only way aronud this would be to drop the constraints and recreate them all after the TRUNCATE — dalexsoto, Jan 21 '11 at 09:03
As MySQL does not have a way to temporarily disable a constraint, that's the only solution I see as well (or analyze the FK relationship and then do the truncate in the correct order, but that is quite complicated) — , Jan 21 '11 at 09:08
I'll work on it tomorrow and post back the modified store procedure that handles FK :) — dalexsoto, Jan 21 '11 at 09:22

Carlos Mora · Answer 2 · 2011-01-21T08:02:41.007

0

create procedure drop_tables_like(pattern varchar(255), db varchar(255))
begin
select @str_sql:=concat('drop table ', group_concat(table_name))
from information_schema.tables
where table_schema=db and table_name like pattern;

prepare stmt from @str_sql;
execute stmt;
drop prepare stmt;
end

then call

call drop_tables_like('%', 'dababase_name')

edited Jan 21 '11 at 08:02

answered Jan 21 '11 at 07:56

Carlos Mora

1,164
2
12
28

The problem is that if the customer creates tables without the same patterns this wont help ie: tblCust, tblProducts, custProducts – dalexsoto Jan 21 '11 at 08:00

How can I truncate all tables from a MySQL Database?

2 Answers2

Linked