Truncate multiple tables in one MySQL statement

sql truncate
user2206834 picture user2206834 · Apr 10, 2013 · Viewed 95.3k times · Source

Is there a possibility to truncate with one SQL statement, multiple tables?

Like this:

 truncate table #OBJ_AvailabilityTraining, #OBJ_AvailabilityHoliday, #Dates_temp;

Regards

Answer

Pankaj Agarwal picture Pankaj Agarwal · Apr 10, 2013

You can use the sp_MSforeachtable stored procedure like so:

USE MyDatabase
EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'

Or you can create SQL Statement

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

and run this above SQL statement

Related questions

What's the difference between TRUNCATE and DELETE in SQL

What's the difference between TRUNCATE and DELETE in SQL? If your answer is platform specific, please indicate that.

Read More
I got error "The DELETE statement conflicted with the REFERENCE constraint"

I tried to truncate a table with foreign keys and got the message: "Cannot truncate table because it is being referenced by a FOREIGN KEY constraint". I read a lot of literature about the problem and thought that I found …

Read More
Truncate with condition

truncate ->this resets the entire table, is there a way via truncate to reset particular records/check conditions. For ex: i want to reset all the data and keep last 30 days inside the table. Thanks.

Read More