Truncate Multiple Tables in One MySQL Statement

Truncate multiple tables in one MySQL statement

No, you can only truncate a single table with TRUNCATE command. To truncate multiple tables you can use T-SQL and iterate through table names to truncate each at a time.

DECLARE @delimiter CHAR(1),
        @tableList VARCHAR(MAX),
        @tableName VARCHAR(20),
        @currLen INT

SET @delimiter = ','

SET @tableList = 'table1,table2,table3'

WHILE LEN(@tableList) > 0
BEGIN
    SELECT @currLen = 
    (
        CASE charindex( @delimiter, @tableList ) 
            WHEN 0 THEN len( @tableList  ) 
            ELSE ( charindex( @delimiter, @tableList  ) -1 )
        END
    ) 

    SELECT @tableName = SUBSTRING (@tableList,1,@currLen )

    TRUNCATE TABLE @tableName

    SELECT tableList = 
    (
        CASE ( len( @tableList ) - @currLen  ) 
            WHEN 0 THEN '' 
            ELSE right( @tableList, len( @tableList ) - @currLen  - 1 ) 
        END
    ) 
END

You can have all your table names comma separated in @tableList variable and yes you can truncate multiple tables from different schemas if they are prefixed.

Unable to truncate multiple table at a time

to truncate multiple table

SELECT concat('TRUNCATE TABLE ', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME in ('table1','table2','table3')

or
run it one by one

truncate table table1

Truncate in multiple tables

DECLARE @CODE NVARCHAR(MAX) = '', @XML XML

SET @XML = STUFF((
            SELECT ' ' + data.CODE_DELETE
            FROM (
                SELECT CONCAT ('TRUNCATE TABLE ', table_schema, '.', TABLE_NAME, '') AS CODE_DELETE
                FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_NAME IN ('CITY', 'WORKERS')
                ) [data]
            FOR XML PATH('')
            ), 1, 1, '')
SET @CODE = CAST(@XML AS VARCHAR(MAX))

SELECT @CODE

EXECUTE sp_executesql @CODE;

the variable @XML is to be able to convert the rows into a single value, then we must convert the sql code to varchar in order to execute it with
EXECUTE sp_executesql @CODE;

you can see the code that will be executed if you do this:

SELECT @CODE

city ​​and workers are tables that I have in my database, if you look I change table_schema by TABLE_NAME in the WHERE clause.

How to truncate multiple tables if the table has dependencies

I found the answer for this. This is how I truncated my table.

Truncate table AEParam
ALTER TABLE [dbo].[AEParam] DROP CONSTRAINT [FK_AEvent _AEParam]
Truncate table AEvent
ALTER TABLE [dbo].[AEParam]  WITH NOCHECK ADD CONSTRAINT [FK_AEvent_AEParam] FOREIGN KEY([eID])
REFERENCES [dbo].[AEvent] ([eID])

PHP & MySQL: Truncate multiple tables

thanks for the help guys! here is my answer,

# truncate data from all table
# $sql = "SHOW TABLES IN 1hundred_2011";
# or,
$sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE '".DB_NAME."'";

# use the instantiated db connection object from the init.php, to process the query
$tables = $connection -> fetch_all($sql);
//print_r($tables);

foreach($tables as $table) 
{
    //echo $table['TABLE_NAME'].'<br/>';

    # truncate data from this table
    # $sql = "TRUNCATE TABLE `developer_configurations_cms`";

    # use the instantiated db connection object from the init.php, to process the query
    # $result = $connection -> query($sql);

    # truncate data from this table
    $sql = "TRUNCATE TABLE `".$table['TABLE_NAME']."`";

    # use the instantiated db connection object from the init.php, to process the query
    $result = $connection -> query($sql);
}

---

Related Topics