Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
MySQL query to check if database is empty or not?
You can use INFORMATION_SCHEMA.COLUMNS to check if a database is empty or not. The syntax is as follows −
SELECT COUNT(DISTINCT `TABLE_NAME`) AS anyAliasName FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `table_schema` = 'yourDatabaseName';
The above syntax returns 0 if the database has notable otherwise it returns the number of tables. For our example, we are using the databases ‘sample’ and ‘test3’, which we created before.
The first database ‘sample’ has more tables, therefore the above query will return a number of tables. The second database ‘test3’ does not have any tables, therefore the above query will return 0.
Case 1 − Database sample
The query is as follows −
mysql> SELECT COUNT(DISTINCT `table_name`) AS TotalNumberOfTables FROM `information_schema`.`columns` WHERE `table_schema` = 'sample';
The following is the output displaying the number of tables in it, therefore the database isn’t empty −
+---------------------+ | TotalNumberOfTables | +---------------------+ | 130 | +---------------------+ 1 row in set (0.01 sec)
Case 2 − Database test3
The query is as follows −
mysql> SELECT COUNT(DISTINCT `table_name`) AS TotalNumberOfTables FROM `information_schema`.`columns` WHERE `table_schema` = 'test3';
The following is the output returning 0, therefore the database is empty −
+---------------------+ | TotalNumberOfTables | +---------------------+ | 0 | +---------------------+ 1 row in set (0.00 sec)
As mentioned above, if we get 0, that would mean there are no tables in the database.
1K+ Views
