3

I would like to truncate all the rows of the tables in a mysql database.

SET FOREIGN_KEY_CHECKS = 0; -- Disable foreign key checking.
-- Need MySQL Query to iterater/loop and truncate all the tables or delete all the rows of the -- table
SET FOREIGN_KEY_CHECKS = 1; -- Enable foreign key checking.

Could someone help me with the query please?

Thanks.

xlecoustillier
  • 16,183
  • 14
  • 60
  • 85
user2323036
  • 1,515
  • 5
  • 19
  • 27
  • possible duplicate of [Truncate all tables in a MySQL database in one command?](http://stackoverflow.com/questions/1912813/truncate-all-tables-in-a-mysql-database-in-one-command) – Brian Apr 15 '15 at 00:26
  • Try looking here: http://stackoverflow.com/a/17738975/1124929 – Brian Apr 15 '15 at 00:29

2 Answers2

1

because u used this table primary key as foreign key in some other table. *Delete values from child table and then try to truncate parent table *

eg: country table -> country id(primary key),name
people table -> people id(primary key), People name, country id(foreign key)

delete values from people table and then try to truncate country table :)

praveenraj4ever
  • 384
  • 1
  • 5
  • 14
  • I need a generic query to disable all constraints and truncate all rows..i.e i want the structure to be maintained but all the rows of the all the tables in a database to be delete by ignoring any constraints such as foreign key, primary key, unique key etc., – user2323036 Aug 01 '13 at 17:54
  • Your question is similar to this [post](http://stackoverflow.com/questions/1233451/delete-from-two-tables-in-one-query). – praveenraj4ever Aug 02 '13 at 12:06
  • the query should be generic to be used in any mysql database to truncate all the tables..specificying names will make the query as DB dependent. – user2323036 Sep 27 '13 at 05:07
  • This does not answer the question (which is actually "how do I truncate all tables"), but provides a workaround. – Brian Apr 15 '15 at 00:23
1

This query would do it:

SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) 
FROM INFORMATION_SCHEMA.TABLES
Taryn
  • 242,637
  • 56
  • 362
  • 405
Hashid Hameed
  • 878
  • 1
  • 8
  • 23