Sunday, September 6, 2009

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!

3 comments:

  1. Glynn, great to see you joining the blogging world!

    The coolest thing about SQL from SQL is that it is easy to do things with concurrency, when MySQL has no support:

    shell> mysql -NB -e "select concat('SELECT SLEEP(100) FROM ', table_name, ';') from information_schema.tables where table_schema = 'world'" | xargs -P 16 -I % mysql world -e '%'

    Replace SLEEP with a REPAIR or OPTIMIZE command ;) I just did this as a demo so you can see it in your processlist.

    ReplyDelete
  2. Many thanks for your themed, a good post for me.
    Now it will be very cold in winter, in order to keep warm yourself, Moncler company has provide jackets for you, so it is better to own Moncler Jackets yourselif as soon as possible. Eeveryone love fashion clothing, Polo Ralph Lauren is very popular all over world, that is my dream to get Ralph Lauren.We know Ray Ban by America soldier, all of them wear Ray Ban Sunglasses when they walk on the road, it is fashionable for you. Most of people like to wear jeans, it is very modern when wearing True Religion Jeans in the street.

    ReplyDelete