Open In App

Truncate All Tables in MySQL

Last Updated : 12 Dec, 2022
Improve
Improve
Like Article
Like
Save
Share
Report

TRUNCATE statement is a Data Definition Language (DDL) operation that is used to mark the extent of a table for deallocation (empty for reuse). The result of this operation quickly removes all data from a table, typically bypassing a number of integrity-enforcing mechanisms. 

In SQL, truncate is used to delete all data from the table but doesn’t delete the table structure. However, it drops all the rows in one go, once executed.

Syntax: 

TRUNCATE TABLE TABLE_NAME;

Truncate is useful in places where we need to remove all the records of the table. It saves time and is usually faster. However, unlike the Drop statement which deletes both the data and structure, truncate just deletes the data and preserves the table structure that can be utilized later.

Steps for Truncate All Tables in Mysql:

Step 1: Create a database called GeeksforGeeks using the following query:

Query:

CREATE DATABASE GeeksForGeeks;

 

Step 2:  Now we will use that database using the given command:

Query:

USE GeeksForGeeks

 

Step 3: Now we will create a table called student having 4 columns, 1) id 2) roll_no 3) name 4) city 

Query:

CREATE TABLE STUDENT (ID VARCHAR2(20), 
ROLL_NO INT, NAME VARCHAR2(50),
CITY VARCHAR2(50));

 

Step 4: To insert 4 rows in the table, execute the following query:

Query

INSERT INTO STUDENT VALUES ( 'S111', 1, 'RAHUL', 'DELHI');
INSERT INTO STUDENT VALUES ( 'S121', 2, 'RAJ', 'MUMBAI');
INSERT INTO STUDENT VALUES ( 'S131', 3, 'JOE', 'BANGALORE');
INSERT INTO STUDENT VALUES ( 'S141', 4, 'RUTU', 'KOLKATA');

 

 Step 5: To display all the values of a table, execute select:

Query:

SELECT * FROM STUDENT;

 

 Step 6:  To flush all the data from a table, we will use the truncate command:

Query: 

TRUNCATE TABLE STUDENT;

 

Step 7: After truncating we will check what’s inside our table by using a select statement:

Query

SELECT * FROM STUDENT;

 

Step 8: All the rows (data) have been deleted in a single go. But the table structure prevails and that can be checked using following command:

Query

exec sp_columns STUDENT;

Output

 

That is the basic purpose of the truncate command. It will keep the schema as it is for later use and simply clears all the values of a table, which is one of the reasons for it being faster. But the disadvantage is that we cannot roll back once a truncate is executed. So the table contents are not retrieved.


Previous Article
Next Article

Similar Reads

TRUNCATE() Function in MySQL
In this article, you will see how the TRUNCATE() function works. The TRUNCATE function in MySQL is used to truncate a number to a specified number of decimal places. Syntax : TRUNCATE( X, D) Parameter : TRUNCATE() function accepts two parameters as mentioned above and described below. X -The number which to be truncated. D -Number of decimal places
5 min read
Drop Multiple Tables in MySQL
DROP statement in a relational database management system (RDBMS) is used to delete a database object such as a table, index, or view. It can be used to delete the complete database also. The general syntax of the DROP command is as follows: Syntax: DROP object object_name; Example: DROP DATABASE db; In everyday life scenarios and live projects, we
3 min read
Difference between DROP and TRUNCATE in SQL
Prerequisite - DROP, and TRUNCATE in SQL 1. DROP : DROP is a DDL(Data Definition Language) command and is used to remove table definition and indexes, data, constraints, triggers etc for that table. Performance-wise the DROP command is quick to perform but slower than TRUNCATE because it gives rise to complications. Unlike DELETE we can’t rollback
2 min read
SQL Truncate
SQL Truncate is one of the Data Definition Language (DDL) commands. It deletes all the records in a database table. However, it does not delete the table’s structure; instead, it simply reconstructs the structure. The table’s original structure is retained while all the records are removed. Because it reconstructs the structure of a database, it is
4 min read
Difference between DELETE and TRUNCATE
DELETE is a DML(Data Manipulation Language) command and is used when we specify the row (tuple) that we want to remove or delete from the table or relation. The DELETE command can contain a WHERE clause. If the WHERE clause is used with the DELETE command, then it removes or deletes only those rows (tuples) that satisfy the condition; otherwise, by
4 min read
Difference between DELETE, DROP and TRUNCATE
1. DELETE : Basically, it is a Data Manipulation Language Command (DML). It is used to delete one or more tuples of a table. With the help of the “DELETE” command, we can either delete all the rows in one go or can delete rows one by one. i.e., we can use it as per the requirement or the condition using the Where clause. It is comparatively slower
3 min read
DROP and TRUNCATE in SQL
DROP and TRUNCATE in SQL remove data from the table. The main difference between DROP and TRUNCATE commands in SQL is that DROP removes the table or database completely, while TRUNCATE only removes the data, preserving the table structure. Let's understand both these SQL commands in detail below: What is DROP Command?DROP command in SQL is used to
4 min read
Find all Tables that contain a specific Column name in SQL Server
As a SQL DBA, we might need to write a SQL Query to Find all Tables that Contain Specific Column Name with example. Below screenshot will show you the tables inside the database 'SQL_DBA' Find all tables that contain a specific column name in SQL Database : In the below example, we are using INFORMATION_SCHEMA.COLUMNS to get the table names where t
2 min read
How to List All Tables in Oracle?
In this article, we will discuss all the methods to list all tables in the oracle SQL Database. We have three types of a subset of tables available to use as identifiers which in turn help us to sort the required table names. Here, are the following types of table identifiers in the Oracle SQL Database. 1. DBA_tables: If the user is SYSTEM or has a
2 min read
How to List All Tables in a Schema in Oracle Database?
Prerequisite: Create Database in MS SQL Server There are multiple ways to list all the tables present in a Schema in Oracle SQL. Such ways are depicted in the below article. For this article, we will be using the Microsoft SQL Server as our database. Method 1: This method lists all the information regarding all the tables which are created by the u
2 min read
Article Tags :