Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

H2 - How to truncate all tables?

Tags:

I assume there is a way of doing this from code, at least some good workaround.

Please consider that I do not want to delete all tables (I've seen this command), just to delete rows from them but to keep existing schema and all constraints.

Maybe I can somehow obtain the list of all tables from metadata and apply TRUNCATE command for each separately? But what about their relations and foreign keys?

Any Idea?

like image 430
Filip Avatar asked Nov 20 '14 17:11

Filip


People also ask

How do I truncate all tables?

Then truncate all tables, on by one: TRUNCATE TABLE table1; TRUNCATE TABLE table2; TRUNCATE TABLE table3; Or you can print out the truncate statements all at once by executing this query: SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.

How do I delete a table from H2 database?

Try this following options. Drop Table: Drop Table is a command that deletes the respective table and its structure. DROP TABLE TABLE_NAME; Drop Schema:Drop Schema is a command that drops a respective schema from the database server.

Does truncate delete data permanently?

It Removes all the data. Truncate table cannot activate a trigger because the operation does not log individual row deletions. Faster in performance wise, because it doesn't keep any logs.


1 Answers

You may do it this way:

  • Disable referential integrity using SET REFERENTIAL_INTEGRITY FALSE

  • Get the list of all tables using SHOW TABLES

  • Delete the data from each table using TRUNCATE TABLE tableName

  • Enable referential integrity using SET REFERENTIAL_INTEGRITY TRUE

like image 54
Julian Ladisch Avatar answered Sep 18 '22 05:09

Julian Ladisch