Using SQL to generate SQL

Photo by Sunder Muthukumaran on Unsplash

Recently i was working on converting all the tables in my database from latin1 to utf8mb4. This work required me to write a lot of SQL queries which was taking a lot of time.So i did some research on how to avoid writing these queries manually. here’s what i found.

Note : in this example we are considering the database as mysql

The information_schema is your friend.

My first challenge was to find out names of all tables which were in latin1.Here’s where the database information_schema was helpful you can read more about it on https://dev.mysql.com/doc/mysql-infoschema-excerpt/8.0/en/information-schema.html.

To fetch the table names which has character set as latin1 we can use the following query.

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_COLLATION LIKE ‘%latin1%’;

Finding the dynamic Element.

To convert the data from latin1 to utf8mb4 we can use the following query.

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4;

now if you notice in the above query we only need to change the table_name to convert each table rest of the SQL statement is just a constant string.

The power of concatenation.

Till now we learned how to fetch the table names we also know what the query to convert the tables is.Now we just have to put everything together we need to fetch the table names and put them in our query, this can be done using the mysql CONCAT function you can read more about it at https://www.w3schools.com/sql/func_mysql_concat.asp

the final query which will generate all required queries for us would look something like this

SELECT CONCAT(‘ALTER TABLE ‘,TABLE_NAME,’ CONVERT TO CHARACTER SET utf8mb4;’) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_COLLATION LIKE ‘%latin1%’;

You can take this example and apply to whatever your use-case is.

For example lets say you have find count of all rows present in all tables in your database you can write a SQL query to generate all SQL statements for it.

SELECT CONCAT(‘SELECT COUNT(*) FROM ‘,TABLE_NAME,’;’) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA =’schema_name’;

once you start using SQL to generate SQL things start to get magical.