I recently learned how to use MySQL to generate MySQL statements for several admin-type Data Definition Language (DDL) SQL queries I needed to execute on the server. For many DBA’s this is likely nothing new and in fact, is a well-used tool in their kit. However, I mostly focus on developer-related tasks, so it is a fresh-in-my-mind learning experience. Continue reading for more information…
Some backstory for context
I’ve recently been porting over an existing vanilla PHP application to CodeIgniter 4. One of the new features is bootstrapping all of the necessary MySQL CREATE TABLE statements dynamically based on information parsed from a .fxl file (which is close kin to a .xml file structure-wise).
Each CREATE TABLE statement is complete with FOREIGN KEY constraint clauses to ensure data integrity between linking tables.
Wanting to perform several smoke tests for the table creation process, I came to a situation in which I needed to DROP the FOREIGN KEY for each table, and then DROP all the tables once each constraint had been disabled. With 60 plus tables, executing that many statements by hand are just not feasible.
What do I do?
Google and learn specific queries against the INFORMATION_SCHEMA database along with using the CONCAT() function, and generating all the needed SQL statements.
Although I won’t use the tables and data I work with within the application I’m building, I’ll use the familiar sakila practice database for the examples.
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
SQL to SQL
The KEY_COLUMN_USAGE table in the INFORMATION_SCHEMA database is chock-full of goodies. The following query returns all the constraints for all the tables in the ‘sakila’ database:
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = 'sakila';
Related: The REFERENTIAL_CONSTRAINTS_TABLE in the INFORMATION_SCHEMA database is another great source of information on FOREIGN KEYS.
Suppose for whatever reason we want to drop the FOREIGN KEY constraint for each table? That’s a great number of ALTER TABLE statements
Here we can use a little craftiness and the CONCAT() function to generate all the necessary DDL statements with this SELECT query:
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = 'sakila'
AND
REFERENCED_TABLE_NAME IS NOT NULL;
All of the individual ALTER TABLE statements can be saved to a script and run whenever needed.
Support my blog by visiting my Tip Jar. Thank you so much! Every bit helps.
MySQL to MySQL – Adding a column to multiple tables
Based on the same concept, if we needed to add an identical column to all the tables in the schema, we can use the CONCAT() function and a SELECT query against the same table in the INFORMATION_SCHEMA and produce the necessary ALTER TABLE statements as shown below:
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = 'sakila'
Note: Scripting out mass DDL statements like this is a great skill to have. However, you must exercise caution because you are applying changes to a large number of tables in your database, for better or worse!
It’s worth noting: Also, this is my first encounter with the tables in the INFORMATION_SCHEMA database. Please let me know if the example queries are inaccurate or inadequate for their purposes and if any other alternatives or corrective measures are needed.
What are your favorite scripting tips and tricks?
I’d love to know what your go-to scripting tips are. Please share them in the comments below if you would like and many thanks!
As always, if you have any questions or see any mistakes in the code, please let me know via the comments. Constructive comments help me provide accurate blog posts and are much appreciated. Thank you for reading
Are you a Medium member? If so, receive an email notification each time I publish a blog post if you prefer the Medium platform. Not a member? No worries! Use my sign-up link (I will get a commission at no extra cost to you) and join. I really enjoy reading all the great content there and I know you will too!!!
Thank you for reading this post. Please share it with someone else who would enjoy it as well.
Josh Otwell has a passion to grow as a PHP Developer, SQL expert, and technical blogger/writer.
Disclaimer: The majority of examples in this post, are performed in a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Like always, just because you can do something doesn’t mean you should. My opinions are my own.
Promotional: I am making Gmail HTML Email Signature Templates over in my Etsy shop. Make your emails stand out and pop with your very own.
One thought on “Use MySQL to generate MySQL for Admin tasks”