I am trying to truncate all the tables in a MySQL database. I read this posting, and then tried the following in the windows command line:
mysql -u user_name -p -Nse 'show tables' database_name | while read table; do mysql -e "truncate table $table" database_name; done
Notice that I added -u user_name -p to the code given in the most popular answer to the question above (given by battousaix). But the problem is that MySQL is throwing the following error:
`while` is not recognized an an internal or external command, operable program, or batch file.
This question is different than the one in my link above because this question may have to do with the windows command line.
EDIT:
I tried the following in a sql file that I called from the MySQL command line:
use mydbname;
SET FOREIGN_KEY_CHECKS=0
SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES where table_schema in (mydmname);
SET FOREIGN_KEY_CHECKS=1
Note that mydbname was the actual name of my database, but all the other terms in the above code are exactly as shown here. Do I have to give each table name explicitly? Note that the example at the start of my posting would do all the tables in one command. here is the error thrown by the 5 line source file shown in this edit:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'SELEC
T Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';')
FROM INFORMAT' at line 3
How can I alter the code above to successfully truncate all the tables in the MySQL database from the windows command line? I could also happily use the source command from the MySQL command line if I knew what to put in the sql file.
