Export database schema as ASCII from mySQL so I can post it on SO

Question

When I need to ask a mySQL database schema question - I try and "write" out my table schema - but its quite long.

so I checked SO - but I couldnt find an answer: is there a way to automatically generate an ASCII style database schema from mySQL (or phpmyadmin) automatically?

I.e. I want to get something like this automatically:

+----------+---------+----------------------------+
|  user_ID | user    |   roles                    |
+----------+---------+----------------------------+
|        1 |   Smith |    1                       |
+----------+---------+----------------------------+
|        2 |   Jones |    1                       |
+----------+---------+----------------------------+

how do people do it? What is the best way?

Answer 1

Am not certain phpMyAdmin can do what you want, but you can get exactly this by using the mysql prompt. Here's how:

Assuming you want just the schema of a table my_table.

Open a terminal and run mysql passing in the username (-u) and password (-p) if required. On windows, you might want to ensure that the mysql bin directory is on the path (if you are working on Windows, you might also want to have mysql on the PATH).

Once you have a mysql terminal;

you might want to see which databases are available:

show databases;

Select the database to use:

use this_database;

You might also wish to see which tables are available (in this database):

show tables;

Now, to see the schema / definition of a table e.g my_table, run the following command:

describe my_table;

That should get you an ASCII-pretty-printed ouput like this for example:

+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| id                 | int(11)      | NO   | PRI | NULL    | auto_increment |
| name               | varchar(50)  | NO   |     | NULL    |                |
| location_id        | int(11)      | NO   | MUL | NULL    |                |
| manager_id         | int(11)      | NO   | MUL | NULL    |                |
| other              | varchar(100) | YES  |     | NULL    |                |
| creation_timestamp | datetime     | NO   |     | NULL    |                |
| is_active          | tinyint(1)   | NO   |     | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+

In case it's the schema+data that you want, then you are probably settling for the column names, and whatever rows you want, with a SELECT query. For example something like this:

SELECT * FROM stores LIMIT 2;

Will give the following:

+----+------------+-------------+------------+-------+---------------------+-----------+
| id | name       | location_id | manager_id | other | creation_timestamp  | is_active |
+----+------------+-------------+------------+-------+---------------------+-----------+
|  1 | Good Store |           1 |          1 |       | 2012-06-17 19:14:15 |         1 |
|  2 | Neu Store  |           2 |          1 |       | 2012-06-29 05:14:24 |         1 |
+----+------------+-------------+------------+-------+---------------------+-----------+

NOTE : In all the above examples, mysql will surely get you the output as I've shown (unless if paging or the orientation is changed), but gor you to then copy and paste this output onto SO, you need to copy and paste from the terminal / console; on standard linux/unix terminals, you can select the output using the mouse, and then copy it onto the clipboard using Control+Shift+C, then paste it onto SO. On windows, after selecting with the mouse, I think u can then right click and select copy or you need to select mark from the console window, and then select the output, after which it should be automatically available on the clipboard for you to paste on SO.

Also, when you paste the output into the textbox on SO, don't forget that for SO to display it as code, you need to select the content, and then click the {} to have it formatted as code. Alternatively, just left-indent it with 4 spaces for each line.

Hope this resolves you problem, though it's not as automatic as you might have wished for, but I believe a script using something like xclip and the mysql commands could get close to this, done automatically :-)

Answer 2

Your question refers actually to printing the table data, not the schema. And if I understand it correctly, you're looking at printing out the data in pretty format, with the fancy frames and all.

In which case, take a look at Ways to export MySQL result set to file on client side.

In particular, you can use the tee command from within the MySQL command line, which duplicates everything you see on the command line to file. For example:

mysql> tee /tmp/nice_output.txt
mysql> SELECT 1 AS one, 2, 3 FROM DUAL;
+-----+---+---+
| one | 2 | 3 |
+-----+---+---+
|   1 | 2 | 3 |
+-----+---+---+

Now open the file using your favorite editor and find:

mysql> SELECT 1 AS one, 2, 3 FROM DUAL;
+-----+---+---+
| one | 2 | 3 |
+-----+---+---+
|   1 | 2 | 3 |
+-----+---+---+
1 row in set (0.00 sec)

Which is a copy of what you just typed and got on command line.

Answer 3

In phpMyAdmin, select your database first, then go to the Export feature, and keep Structure ticked, untick Data, and untick Save As File. Then, click Go. In general I export all tables in one go, but you can choose which to export using the select chooser on the left.

Edit: I just noticed you asked for a 'schema', but your illustration is of both schema and data. Although you can do an export of both in phpMyAdmin, this won't be well-formatted as your illustration shows. Perhaps the best way would be to do an export as CREATE TABLE and INSERT, then add it to a SQLFiddle? That has the advantage that people answering your question can fork the example and experiment easily.