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.