How To Write Code
That Doesn't Suck

wry observations from the deep end of the software cesspool

2012-06-19

How to dump SQL for all views in a mysql database

I often generate a fair number of views while developing reporting SQL, and have off an on looked for a way to easily save their source code to a file. mysqldump doesn't directly support extracting only views, but with a little command line trickery and a query against INFORMATION_SCHEMA you can make it do the right thing:

mysql -u username INFORMATION_SCHEMA
      --skip-column-names --batch
      -e "select table_name from tables where table_type = 'VIEW'
          and table_schema = 'database'"
      | xargs mysqldump -u username database
      > views.sql

The skip-column-names and batch options produce output with just one view name per line, which is what xargs needs as input. Be sure to replace both occurences of username and database with appropriate values, and add -h for remote hosts and -p if the user requires a password. Here's a one-line example for user root with no password on localhost, with a database named "foo":

mysql -u root INFORMATION_SCHEMA --skip-column-names --batch -e "select table_name from tables where table_type = 'VIEW' and table_schema = 'foo'" | xargs mysqldump -u root foo > views.sql