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