TRUNCATE data from all tables in a Database

Question

For some work, the requirement is such that we want to retain the table and database structure while truncating all data in the multiple table at one go. Since Truncate Table_name only truncates one table at time. Is there a way to truncate multiple table? Help would be appreciated.

Answer 1

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}

Answer 2

Do you have relationship in your tables ? If so you cannot truncate Tables .. However to remove all data & Reseed Identity use this query

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
--Disable all triggers
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL' 
--Delete 
-- Then reseed if Table has identity column
EXEC sp_MSForEachTable 'IF (''?'' NOT LIKE  ''%CNF_tb%'')
                        BEGIN
                            DELETE from ? 
                            if @@RowCount >0
                            BEGIN
                                IF (Select  OBJECTPROPERTY(OBJECT_ID(''?''),''Tablehasidentity'') 
                                    FROM    Sys.Tables 
                                    Where   object_id=OBJECT_ID(''?''))=1
                                BEGIN
                                    DBCC CHECKIDENT(''?'' ,Reseed,0) 
                                END
                            END
                        END 
                        ELSE
                        BEGIN
                            Select ''?'' as ''Not Changed''
                        END'


--Enable all constraints
EXEC sp_MSForEachTable 'ALTER TABLE ? With Check CHECK CONSTRAINT ALL' 
--Enable all triggers
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'

Answer 3

Unfortunately there's no solution to truncate all tables at once.

For those using PHP/PDO, I came up with the following solution:

$pdo->query('SET FOREIGN_KEY_CHECKS = 0;');
$tables = $pdo->prepare('SHOW TABLES');
$tables->execute();
foreach ($tables->fetchAll(\PDO::FETCH_COLUMN) as $table)
   $pdo->query('TRUNCATE TABLE `'.$table.'`')->execute();
$pdo->query('SET FOREIGN_KEY_CHECKS = 1;');