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
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.
information_schema database consists
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;
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.