# MySQL query to check if database is empty or not?

MySQLMySQLi Database

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.

Published on 19-Mar-2019 08:46:40