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

2 comments:

f010bd22-4760-11e4-9681-a35db6c405e7 said...

way to go, putting up ths way to dump a view. What is wrong with the MySQL gods, they don't know people need to be able to dump views, and they don't bother making it non-braindead? And what about phpmyadmin, are they asleep on the job?

Sreedhar D said...

Good article..while restoring the dump need to re look on definer in the function creation body

Post a Comment