Delete all record exist in all table.

Question

Hello there, How to make query in sql to delete all record exist in every table.

There possible to write every name table using command delete. But it will take long time to write every single name table.

I am already try DELETE FROM * in MySQL but it got error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*' at line 1

Regard.

Answer 1

You will just have to do a TRUNCATE query for each table. You could also use delete, but truncate is generally more appropriate for what you are doing.

TRUNCATE TABLE table1;
TRUNCATE TABLE table2;
TRUNCATE TABLE table2;

There is no * or all table selector.

Answer 2

I believe MySql has an sql statement as follows:

Show tables

Create your command object with Show tables as the command text and execute.

Using that statement you could execute the query and fill MySqlDataReader. Iterate the reader and put table names in a formatted string as follows.

// create connection string
...
while (myDataReader.Read())
{
    // execute command
    string command = "TRUNCATE TABLE {0}";

    MySqlCommand myCommand = new MySqlCommand();
    myCommand.CommandText = string.Format(command, reader[0]);

    using (MySqlConnection myConnection = new MySqlConnection(myConnString))
    {
        myCommand.Connection = myConnection;
        myCommand.Connection.Open();

        myCommand.ExecuteNonQuery();
    }
}

This ought to be close enough to help you get on the right track.

Answer 3

You can use the INFORMATION_SCHEMA to iterate over all the tables and dynamically execute the DELETE or TRUNCATE statement.

Answer 4

DELETE statement can be used.

Following will delete all the rows but indexes(e.g. auto increments) will not be reseted.

DELETE * FROM TABLEX

Following will delete all the rows also indexes will be reset

TRUNCATE TABLEX

Answer 5

Hello,

Please use this

<?php
mysql_connect('localhost', 'user', 'password');
$dbName = "database";
mysql_select_db($dbName)
$result_t = mysql_query("SHOW TABLES");
while($row = mysql_fetch_assoc($result_t))
{
   mysql_query("TRUNCATE " . $row['Tables_in_' . $dbName]);
}
?>

All the best

Answer 6

If you want to automate this, you have to write some code external to MySQL. Use show tables to get the table names and then call TRUNCATE on each of them.