SQL from SQL

This is a simple technique, to write a report that generates SQL syntax, that you can then turn around and execute. I've heard this called "SQL from SQL" or sometimes "dynamic SQL"--although it doesn't use prepared statements or anything like that.

There are a few simple options in SQL*Plus (Oracle's command-line tool) to accomplish this, and a few simple--but different--options in mysql (MySQL's command-line tool).

Try this line:

shell> mysql --silent --skip-column-names -e "select concat('describe ', table_name, ';') from information_schema.tables where table_schema = 'world'"

(Note I'm supplying my username and password to the session from an option file.) This sends the following to standard output:

describe City;
describe Country;
describe CountryLanguage;

The trick is to select the data you want from your system, combined with the literal syntax you want to generate. It's a simple matter to execute these commands, by directing this output stream into another mysql session:

shell> mysql --silent --skip-column-names -e "select concat('describe ', table_name, ';') from information_schema.tables where table_schema = 'world'" | mysql -v -v -v world

(The -v -v -v options give extra verbose output suitable to this particular report.) If you like, save the final result in a file:

shell> mysql --silent --skip-column-names -e "select concat('describe ', table_name, ';') from information_schema.tables where table_schema = 'world'" | mysql -v -v -v world > worldtables.txt

It should come as no surprise that the meta-SQL statements you write will often use the meta-data in your database: the information_schema tables. You can use this technique to optimize all the base tables in a database, collect checksum reports, and so forth.

Enjoy!

Introduction

Folks ---

I'm inspired by two of my colleagues: Sarah Sproehnle (http://sarahdba.blogspot.com) and George Trujillo (http://mysql-dba-journey.blogspot.com/). Sarah and George are two excellent MySQL instructors, and their blogs clearly work as tools of the trade. I intend to use this space for a similar purpose: to address questions and techniques of general interest that arise in my MySQL classes.

Enjoy!

--- Glynn