Is there a query (command) to truncate all the tables in a database in one operation? I want to know if I can do this with one single query.
-
2Truncate all tables? do you meant truncate all columns of all tables in a database? What query language is this in? (e.g. SQL, MySQL, Oracle, Postgres, etc)Jay– Jay2009-12-16 07:02:28 +00:00Commented Dec 16, 2009 at 7:02
-
Im not sure I would recommend doing this as you could very easily get yourself into trouble. Is there a reason why you cant just script the truncates and run the script?GrayWizardx– GrayWizardx2009-12-16 07:06:50 +00:00Commented Dec 16, 2009 at 7:06
-
thanks 4 the reply but only reason not scripting it is running short of time so thought of runnind a query in mysqldevang– devang2009-12-16 07:11:57 +00:00Commented Dec 16, 2009 at 7:11
-
ya its in mysql..and query means something similar to truncate table table_name..here i want to truncate all rows of all tables in my db in one querydevang– devang2009-12-16 07:18:54 +00:00Commented Dec 16, 2009 at 7:18
-
You can use Information_Schema with a cursor. Not sure about MySql, but it should support Information_Schema.TablesGrayWizardx– GrayWizardx2009-12-16 07:34:33 +00:00Commented Dec 16, 2009 at 7:34
|
Show 5 more comments
31 Answers
<?php
// connect to database
$conn=mysqli_connect("localhost","user","password","database");
// check connection
if (mysqli_connect_errno()) {
exit('Connect failed: '. mysqli_connect_error());
}
// sql query
$sql =mysqli_query($conn,"TRUNCATE " . TABLE_NAME);
// Print message
if ($sql === TRUE) {
echo 'data delete successfully';
}
else {
echo 'Error: '. $conn->error;
}
$conn->close();
?>
Here is code snippet which I use to clear a table. Just change $conn info and TABLE_NAME.
1 Comment
Craig Wayne
hey james, i think the OP is looking for firstly a one liner.... and secondly a command to get all tables once off and delete them. this requires some sort of user intervention. thanks for the post though