- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to get the size of the tables of a MySQL database?
To get the size of the tables of a MySQL database, you can use the “information_schema.tables”.
Here is the syntax to know the size of all tables.
SELECT TABLE_NAME AS `ALLTABLESNAME`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `TABLESIZEIN(MB)` FROM information_schema.TABLES WHERE TABLE_SCHEMA = "yourDatabaseName" ORDER BY (DATA_LENGTH + INDEX_LENGTH) ASC;
Let us apply the above syntax to get the size of the tables.
mysql> SELECT TABLE_NAME AS `ALLTABLESNAME`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `TABLESIZEIN(MB)` -> FROM information_schema.TABLES WHERE TABLE_SCHEMA = "business" -> ORDER BY (DATA_LENGTH + INDEX_LENGTH) ASC;
Sample Output.
+------------------------------------------------------------------+-----------------+ | ALLTABLESNAME | TABLESIZEIN(MB) | +------------------------------------------------------------------+-----------------+ | studentrecordwithmyisam | 0 | | querydatedemo | 1 | | rownumberdemo | 0 | | secondtable | 0 | | spacecolumn | 2 | | table1 | 0 | | tbldemotrail | 2 | | tblstudent | 0 | | timestamptodatedemo | 1 | | uniqueconstraintdemo | 0 | | usernameandpassworddemo | 2 | | addingunique | 5 | | bookindexes | 0 | | tblf | 0 | | uniquedemo | 2 | | multipleindexdemo | 0 | | uniquedemo1 | 0 | | foreigntable | 5 | | tabledemo2 | 0 | | foreigntabledemo | 2 | | studentenrollment | 0 | | tabledemo3 | 0 | | duplicatebookindexes | 0 | | clonestudent | 2 | | student | 0 | +------------------------------------------------------------------+-----------------+ 26 rows in set (10.29 sec)
Updated on 30-Jul-2019 22:30:23
- Related Questions & Answers
- How Can I check the size of the tables in a particular MySQL database?
- How to get the list of tables in default MySQL database?
- How to count the number of tables in a MySQL database?
- Get the storage size of a database in MongoDB?
- How to check table status of the tables in a particular MySQL database?
- Get a list of non-empty tables in a particular MySQL database?
- While connecting to one MySQL database, how can I see the list of tables of other MySQL database?
- What is the MySQL query to display the number of tables in a database?
- How can we get the list of tables in a particular database from MySQL Server command line?
- Get the date/time of the last change to a MySQL database?
- Which tables are used to control the privileges of MySQL database server?
- How can we check the character set of all the tables in a particular MySQL database?
- Get the size of selected rows in MySQL
- List down all the Tables in a MySQL Database
- How to alter the database engine of a MySQL database table?
Advertisements