How Can I Truncate All Tables From A Mysql Database?

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 w

Solution 1:

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

BEGINDECLARE done BOOLEANDEFAULTFALSE; 
    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 databaseDECLARE c1 CURSORFORSELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = "@DatabaseName";
    DECLARE CONTINUE HANDLER FORSQLSTATE'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

Solution 2:

createprocedure drop_tables_like(patternvarchar(255), db varchar(255))
beginselect@str_sql:=concat('drop table ', group_concat(table_name))
from information_schema.tables
where table_schema=db and table_name likepattern;

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

then call

call drop_tables_like('%', 'dababase_name')