Showing posts with label Truncate all tables in a MySQL database in one command. Show all posts
Showing posts with label Truncate all tables in a MySQL database in one command. Show all posts

Tuesday, 10 May 2016

Truncate all tables in a MySQL database in one command

Truncate all tables in a MySQL database in one command

SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('databasename1','databasename2');
 
if Cannot delete or update a parent row: a foreign key constraint fails
 
That happens if there are tables with foreign keys references to the table you are trying to drop/truncate.
Before truncating tables All you need to do is:
SET FOREIGN_KEY_CHECKS=0;
Truncate your tables and change it  back to 
SET FOREIGN_KEY_CHECKS=1; 

user this php code
<?php


mysql_connect("localhost","root",'');

mysql_select_db("restaurant");

$truncate = mysql_query("SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') as tables_query FROM INFORMATION_SCHEMA.TABLES where table_schema in ('restaurant')");


while($truncateRow=mysql_fetch_assoc($truncate)){

mysql_query($truncateRow['tables_query']);


}


?>