MySQL Stored Procedure for truncating all tables in the database.

gistfile1.txt

DELIMITER //
CREATE PROCEDURE TruncateAll()
--
-- TruncateAll
-- Date: 2013-09-29
-- Author: Andrew Cassidy <andrew@cassidywebservices.co.uk>
--
-- This Stored Procedure Loops over the tables in the current database and
-- truncates them. Behaviour of tables containing or referenced by foreign keys
-- is undefined (by me, MySQL documentation may have other ideas, TL;DR)
--
-- This program is free software: you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
-- 
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
-- 
-- You should have received a copy of the GNU General Public License
-- along with this program.  If not, see <http://www.gnu.org/licenses/>.
--
BEGIN
  -- Temporary variable for the table name
  DECLARE tableName NVARCHAR(255);
  -- Wheteher or not the cursor is finished looping over the table list
  DECLARE done INT DEFAULT FALSE;
  -- A cursor over the table list read from the MySQL information schema database
  DECLARE tableCursor CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = (SELECT DATABASE());
  -- Set up the error handler for breaking out of the loop reading the cursor
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  -- Open  the cursor
  OPEN tableCursor;
  -- Start looping over the records in the cursor
  read_loop: LOOP
    -- Read the next item into our tableName variable
    FETCH tableCursor INTO tableName;
  -- If fecth failed (and the error handler set done), exit the loop
    IF done THEN
      LEAVE read_loop;
    END IF;
	-- Create the truncate query
    SET @s = CONCAT('TRUNCATE TABLE ', tableName);
	-- Prepare, execute and deallocate the truncate query
    PREPARE truncateStmt FROM @s;
    EXECUTE truncateStmt;
    DEALLOCATE PREPARE truncateStmt;
	-- On to the next!
  END LOOP;
  -- Close the cursor, all should be cleaned up now
  CLOSE tableCursor;
END//
DELIMITER ;

There is an error in line no 31. it will load all tables including views. if you want to truncate only tables.

Add a filter Table_Type = 'BASE TABLE' in line 31.

DECLARE tableCursor CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE Table_Type = 'BASE TABLE' and TABLE_SCHEMA = (SELECT DATABASE());

can you please tell me how to truncate tables having foreign key constraint.Thanks

To truncate tables having foreign key constraints, use SET FOREIGN_KEY_CHECKS=0; before starting cursor and
use SET FOREIGN_KEY_CHECKS=1; after closing a cursor in above script.