How to List the Empty Databases in MySQL.

Yesterday, I was assigned a task to delete the empty databases from the server for one of the companies I work for Da Manager LTD. At Da Manager we host many websites and provide the cutting-edge solution with our hosting platform to our clients.

Basically, we were experiencing the issue of databases getting duplicated with no tables on it. And going through them individually could have been proved a tedious task. Since, we host thousands of sites consisting of thousands of databases available on our server. That’s when we found the need to delete the databases programmatically. We being the developers have worked with the tables and it’s really easy to operate upon tables, manipulate and list them using the SQL queries. But can we do the same for the Databases? Yes, we can.

Operations On Databases

MySQL being the Relational Database Management system (RBMS) stores the details of the schema and meta information about the databases and other important information on the information_schema database. This database is one of the defaults that’s provided with the MySQL and hence should not be deleted.

The information_schema database consists SCHEMATA and TABLES tables consisting information about the total databases, their schemas and the tables details of those databases respectively. Taking help of these tables we can easily form a SQL query to list the empty databases available our server.

SELECT S.SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA S LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES T ON S.SCHEMA_NAME = T.TABLE_SCHEMA WHERE T.TABLE_SCHEMA IS NULL;

The given above code will return the names of the empty databases under the name (key) SCHEMA_NAME which can later be used with any programming language of your choice that can handle MySQL databases. We can easily loop over the database names provided inside the SCHEMA_NAME key and drop them by performing the SQL query or command.

DROP DATABASE databasename;

We can also delete the multiple databases leveraging the use of concatenation (separating the multiple databases names with a comma).

DROP DATABASE databasename, databasename2, databasename3;

Conclusion

The SQL (Structured Query Language) is a powerful and special purpose domain specific language for querying the data. And it’s designed to perform almost any operation upon the databases using its different types of statements:

  • DML (Data Manipulation Language)
  • DDL (Data Definition Language)
  • DCL (Data Control Language)
  • TCL (Transaction Control Language)

Thanks for reading this post. Let me know about your experience with the post in the comments below. Also, If you like what I write then maybe, you’d consider to support me on Patreon.