Is there a query (command) that truncates all tables in the database in one operation? I wonder if this can be done through a query.

#1 building

I found that even after NOCHECK CONSTRAINT ALL, TRUNCATE TABLE.. still has a foreign key constraint problem, so I used the DELETE FROM statement instead. This does mean that the identity seed will not be reset, and you can always add DBCC CHECKIDENT to implement it.

I use the following code to print the sql used to truncate all tables in the database to the message window before running. This only increases the difficulty of making mistakes.

EXEC sp_MSforeachtable 'PRINT ''ALTER TABLE ? NOCHECK CONSTRAINT ALL'''
EXEC sp_MSforeachtable 'print ''DELETE FROM ?'''
EXEC sp_MSforeachtable 'print ''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'''

#2 building

1. To truncate a table, you must remove the foreign key constraints that map to the columns in that table from other tables (actually on all tables in a particular DB / Schema).
2. Therefore, all foreign key constraints must be removed before table truncation.
3. (optional) after data truncation, use the optimized table (in mysql, in the innodb engine esp) to recycle the used data space / size to the OS.

4. Once the data truncation is performed, create the same foreign key constraint on the same table again. See the script below, which generates the script to do the above.

   SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME,';') FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_SCHEMA='<TABLE SCHEMA>' UNION SELECT CONCAT('TRUNCATE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='<TABLE SCHEMA>' AND TABLE_TYPE='BASE TABLE' UNION SELECT CONCAT('OPTIMIZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='<TABLE SCHEMA>' AND TABLE_TYPE='BASE TABLE' UNION SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ADD CONSTRAINT ',CONSTRAINT_NAME,' FOREIGN KEY(',COLUMN_NAME,')',' REFERENCES ',REFERENCED_TABLE_NAME,'(',REFERENCED_COLUMN_NAME,');') FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME LIKE 'FK%' AND TABLE_SCHEMA='<TABLE SCHEMA>' INTO OUTFILE "C:/DB Truncate.sql" LINES TERMINATED BY '\\n';

Now run the generated Db Truncate.sql script

Benefits. 1) Reclaim disk space 2) no need to delete and recreate DB / Schema with the same structure

Shortcomings. 1) The FK constraint should be the name in the table and the name in the constraint name should contain "FK".

#3 building

Thorne 1)

mysql> select group_concat('truncate',' ',table_name,';') from information_schema.tables where table_schema="db_name" into outfile '/tmp/a.txt';
mysql> /tmp/a.txt;

Thorne 2)

- Export only structure of a db
- drop the database
- import the .sql of structure 

- editing -

earlier in solution 1, i had mentioned concat() instead of group_concat() which would have not returned the desired result

#4 building

I know this is not exactly a command, but you can implement the required results from phpMyAdmin as follows:

1. Select (all) tables to delete (all selected)
2. Select drag and drop / truncation from the selected: List
3. Copy the query (all content with a red background) on the confirmation page ("you really want to:")
4. Go to the top, click SQL and type: "set foreign \ u key \ U checks = 0;" then paste the previously copied query
5. Click "start"

The idea is to get all the tables from the database quickly (you do it in 5 seconds and 2 clicks), but first disable foreign key checking. There is no CLI, nor is the database removed and added again.

#5 building

Truncating multiple database tables on a Mysql instance

SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('db1_name','db2_name');

Use query results to truncate tables

Note: you may receive this error:

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

This happens if there is a table with a foreign key reference to the table to be dropped / truncated.

Before you truncate a table, you need to do the following:

SET FOREIGN_KEY_CHECKS=0;

Truncate the table and change it back to

SET FOREIGN_KEY_CHECKS=1;