Show Me the Views

At this point, I feel like my blog has become a place to store solutions to technical issues that have caused me frustration. Definitely not my intent, but it is what it is.

This week I wanted to do something I thought should be pretty straightforward: dump/export all of the views from a specific MariaDB/MySQL database. I only wanted the views, no tables, none of the data, just the views. There were several partial solutions on other websites, Stackoverflow replies, and from ChatGPT, but nothing I found was a complete solution. The following was my final solution:

mysql --user=root \
      --password=password \
      --host=localhost \
      --skip-column-names \
      --batch \
      -e "SELECT table_name FROM tables WHERE table_type = 'VIEW' AND table_schema = 'the_database_name'" \
      INFORMATION_SCHEMA | xargs \
mysqldump --user=root \
      --password=password \
      --host=localhost \
      the_database_name > the_database-views.sql

Lastly, don’t forget to update both hard coded instances of root, password, localhost, and the_database_name with your actual MariaDB/MySQL username, password, host, and database name.

If you have a better solution, please feel free to let me know and I will update this post accordingly.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.