SQL statement to truncate all data from all tables in the database

Question

Possible Duplicate:
Truncate all tables in a MySQL database in one command?

how to delete all the data from all tables in the database.

http://stackoverflow.com/questions/1912813/truncate-all-tables-in-a-mysql-database-in-one-command — quantumSoup, Jul 08 '10 at 05:44
http://stackoverflow.com/questions/1912813/truncate-all-tables-in-a-mysql-database-in-one-command — Haim Evgi, Jul 08 '10 at 05:45

score 0 · Answer 1 · answered Jul 08 '10 at 05:45

0

use like this,

TRUNCATE `table1`;
TRUNCATE `table2`;
TRUNCATE `table3`;
TRUNCATE `table4`;

answered Jul 08 '10 at 05:45

Karthik

3,221
5
28
38

Thanks pal, but i got many tables, so i thought some way out to do it at once. – Yajuvendra Vant Jul 08 '10 at 13:39

score 0 · Answer 2 · answered Jul 08 '10 at 05:54

0

If you have scripted creation of empty tables, it may be faster to drop and recreate the database, depending on number of tables. It is less typing for sure :-) -

answered Jul 08 '10 at 05:54

Miro A.

7,633
2
24
21

i tried it but the auto generated ids are not set to zero. they continue from the last id that was truncated. – Yajuvendra Vant Jul 08 '10 at 13:38
Add this to your table creation script: ALTER TABLE tablename AUTO_INCREMENT = 1 – Miro A. Jul 08 '10 at 14:15

score 0 · Answer 3 · answered Jul 08 '10 at 05:54

0

You have to do it in single statements. You could create a loop, assigning the next table name to a variable and then use dynamic sql to execute the TRUNCATE statement.

answered Jul 08 '10 at 05:54

Neil Knight

47,437
25
129
188

SQL statement to truncate all data from all tables in the database

3 Answers3