Truncate All Tables in MySQL Database That Match a Name Pattern

Truncate all tables in MySQL database that match a name pattern

Use concat:

SELECT concat('TRUNCATE TABLE `', TABLE_NAME, '`;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'inventory%'

This will of course only generate SQL which you need to copy and run yourself.

TRUNCATE all tables matching name pattern

You trying to execute this statement on "information_schema" database. Read more about this database [https://dev.mysql.com/doc/refman/5.7/en/information-schema.html]

You should not be running statements on the information_schema database (unless you REALLY know what you're doing). The database serves as a "meta" repository that dictates how the server operates. Chances are that you have no need to touch it and you'll likely brick your server if you do.

This is already answered here. [#1044 - Access denied for user 'root'@'localhost' to database 'information_schema'

Restriction to above: This query will work only if the no of table returned by the statement is 1 for more than 1 tables, you will require to use it in iteration.

To make this work for all the table matching the pattern we would require to use stored procedure.

Please change the Procedure name

CREATE PROCEDURE `new_procedure`()
BEGIN
-- Pattern to Match 
SET @pattern = '%_movielist';
-- Temporary Table to Store the Result of The Select Statement

CREATE TEMPORARY TABLE IF NOT EXISTS Table_ToBeTruncated 
    (
       Id int NOT NULL AUTO_INCREMENT,TableName varchar(100),
       PRIMARY KEY (id)
    );

-- Insert all the TableName  to be Truncated 
    insert Table_ToBeTruncated(TableName)
    SELECT distinct concat('TRUNCATE TABLE `', TABLE_NAME, '`;')
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME LIKE @pattern and TABLE_SCHEMA = 'movielist';

-- Declare a variable to count the no of records to be truncated.
SET @count=(Select count(*)from Table_ToBeTruncated);

-- Iterate the list 
WHILE @count> 0 DO

    -- Pick One table from the Temporary Table List;
    SELECT TableName into @truncatelike from Table_ToBeTruncated where ID= @count;

    -- Prepare the statement
    PREPARE stmt FROM @truncatelike;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Decrease the counter.
    set @count = @count- 1;

END WHILE;

drop TEMPORARY TABLE IF EXISTS Table_ToBeTruncated ;

END

TRUNCATE data from all tables in a Database

The easiest way may be the following:

If you have foreign key constraint then temporarily set it OFF.

SET FOREIGN_KEY_CHECKS=0;

To set it ON again:

SET FOREIGN_KEY_CHECKS=1;

---

To truncate all tables under a particular database

SELECT 
CONCAT('TRUNCATE TABLE ',TABLE_NAME,';') AS truncateCommand
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME_HERE';

To truncate all tables out of all databases

SELECT 
    CONCAT('TRUNCATE TABLE ',TABLE_NAME,';') AS truncateCommand
    FROM information_schema.TABLES;

---

And you will get output like that:

TRUNCATE TABLE your_table_1;
TRUNCATE TABLE your_table_2;
TRUNCATE TABLE your_table_3;
TRUNCATE TABLE your_table_4;
TRUNCATE TABLE your_table_5;
TRUNCATE TABLE your_table_6;
TRUNCATE TABLE your_table_7;
TRUNCATE TABLE your_table_8;
.
.
etc..

Now grab these truncate commands and execute all.

_{You can approach this way to avoid the hassle of writing a stored procedure to get it done if and only if it's a one time job}

How can I truncate all tables from a MySQL Database?

Ok, I solved it by myself here is the stored procedure :)

BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE; 
    DECLARE truncatestmnt TEXT; -- this is where the truncate statement will be retrieved from cursor

    -- This is the magic query that will bring all the table names from the database
    DECLARE c1 CURSOR FOR SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = "@DatabaseName";
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE; 

    OPEN c1;

    c1_loop: LOOP
    FETCH c1 INTO truncatestmnt;
    IF `done` THEN LEAVE c1_loop; END IF;
        SET @x = truncatestmnt;
        PREPARE stm1 FROM @x;
        EXECUTE stm1;
    END LOOP c1_loop; 

    CLOSE c1;
END

What I am making its calling all tables from the given database, this will help if the tables inside the given database have no pattern to follow.

So by calling DECLARE c1 CURSOR FOR SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = "@DatabaseName"; and saving results into a cursor I can fetch all the TRUNCATE TABLE x statements generated by the "n" quantity of tables inside the given database, then by just preparing and executing each statement in the cursor it will truncate all the tables inside the given database.

BTW @DatabaseName must be given as parameter to the stored procedure

Hope this helps someone else too :)

Alex

Truncate tables only with on schema

Refer to Link OR try this script

DECLARE @SQL NVARCHAR(MAX) = ''

SELECT @SQL = (
    SELECT 'TRUNCATE TABLE [' + s.name + '].[' + o.name + ']' + CHAR(13)
    FROM sys.objects o WITH (NOWAIT)
    JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
    WHERE o.[type] = 'U'
        AND s.name = 'dbo'
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

PRINT @SQL

--EXEC sys.sp_executesql @SQL

How do I truncate all data in my MySQL database?

You can dump database without data:

mysqldump -u myuser -p --databases --add-drop-database --no-data my_db > my_db.sql

And restore it after that

mysql -u myuser -p < my_db.sql

Only show tables with certain patterns in mysql show tables

show tables like 'pattern';

How to force truncate all tables(which are all innodb) in a database in MySQL?

About the FK constraints, you could disable them with next statements -

SET FOREIGN_KEY_CHECKS = 0;
...DML statements
SET FOREIGN_KEY_CHECKS = 1; -- enable checking

---

Related Topics