Truncate all tables in a MySQL database in one command?

Question

Is there a query (command) to truncate all the tables in a database in one operation? I want to know if I can do this with one single query.

Answer 1

if using sql server 2005, there is a hidden stored procedure that allows you to execute a command or a set of commands against all tables inside a database. Here is how you would call TRUNCATE TABLE with this stored procedure:

EXEC [sp_MSforeachtable] @command1="TRUNCATE TABLE ?"

Here is a good article that elaborates further.

For MySql, however, you could use mysqldump and specify the --add-drop-tables and --no-data options to drop and create all tables ignoring the data. like this:

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE]

mysqldump usage guide from dev.mysql

Answer 2

Im not sure I would recommend doing this as you could very easily get yourself into trouble. Is there a reason why you cant just script the truncates and run the script?

Answer 3

MS SQL Server 2005+ (Remove PRINT for actual execution...)

EXEC sp_MSforeachtable 'PRINT ''TRUNCATE TABLE ?'''

If your database platform supports INFORMATION_SCHEMA views, take the results of the following query and execute them.

SELECT 'TRUNCATE TABLE ' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

Try this for MySQL:

SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES

Answer 4

An idea could be to just drop and recreate the tables?

EDIT:

@Jonathan Leffler: True

Other Suggestion (or case you dont need to truncate ALL tables):

Why not just create a basic stored procedure to truncate specific tables

CREATE PROCEDURE [dbo].[proc_TruncateTables]
AS
TRUNCATE TABLE Table1
TRUNCATE TABLE Table2
TRUNCATE TABLE Table3
GO

Answer 5

I am not sure but I think there is one command using which you can copy the schema of database into new database, once you have done this you can delete the old database and after this you can again copy the database schema to the old name.

Answer 6

Here is a procedure that should truncate all tables in the local database.

Let me know if it doesn't work and I'll delete this answer.

Untested

CREATE PROCEDURE truncate_all_tables()
BEGIN

   -- Declare local variables
   DECLARE done BOOLEAN DEFAULT 0;
   DECLARE cmd VARCHAR(2000);

   -- Declare the cursor
   DECLARE cmds CURSOR
   FOR
   SELECT CONCAT('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES;

   -- Declare continue handler
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

   -- Open the cursor
   OPEN cmds;

   -- Loop through all rows
   REPEAT

      -- Get order number
      FETCH cmds INTO cmd;

      -- Execute the command
      PREPARE stmt FROM cmd;
      EXECUTE stmt;
      DROP PREPARE stmt;

   -- End of loop
   UNTIL done END REPEAT;

   -- Close the cursor
   CLOSE cmds;

END;