MySQL Show or Desc(ribe) All Tables at Once

I was recently getting up to speed on a few new applications and needed to understand the database schema. I found myself repeatedly using the MySQL show tables and desc tables command line queries. After doing this a few times I got a little frustrated so I wrote this handy little shell script that will generate HTML output of an entire database schema to db_schema.html on your desktop.

Example Output - db_schema.html

This script uses the mysql command line “SHOW TABLES” and “DESC <TABLE>” commands.

For those not familiar with the “SHOW tables” command here’s what the output looks like-

mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |


For those not familiar with the “DESC Table” command here’s what the output looks like.


mysql> desc user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |


This script assumes that:

• the mysql username is root
• there is no password
• the db name is dbname


if [ -f ~/Desktop/db_schema.html ]; then
rm ~/Desktop/db_schema.html
fi