CODEWITHSUNDEEP
mysqlsql-delete
Edy Cu
Edy Cu

Reputation: 3352

Delete all record exist in all table

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

Upvotes: 0

Views: 495

Answers (6)

IAbstract
IAbstract

Reputation: 19881

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.

Upvotes: 0

Justin
Justin

Reputation: 8943

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.

Upvotes: 0

user455982
user455982

Reputation:

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

Upvotes: 1

qasimzee
qasimzee

Reputation: 650

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

Upvotes: 0

Russell McClure
Russell McClure

Reputation: 4851

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

Upvotes: 0

Nate Totten
Nate Totten

Reputation: 8932

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.

Upvotes: 1

Related Questions