How to export MySQL db structure to Excel file?

Question

Is there any tool for exporting a MySQL database structure to an Excel file? For instance:

1 ID int(10) not null pri 0 index comment 

Thanks for any help.

Answer 1

Here is an easier way:

• From phpMyAdmin, select your database, then open the Structure tab.
• Scroll down to the bottom of the list of tables.
• Click Data Dictionary.
• Select everything and then copy / paste in Excel.

This method creates a report listing all the tables, as well as all the fields in each table, including the field type if NULL is allowed, the default value, links, comments, and MIME. It also lists all indexes, including type, uniqueness, packed index, and index comments.

Answer 2

You can query information_schema.columns to get the required data:

 SELECT * from information_schema.columns WHERE table_schema = 'db_2_66028' AND table_name = 'tbl'; 

• table_schema is the name of your db
• table_name name of the table. If you omit this, you will request column information for all of your tables.

See http://sqlfiddle.com/#!2/23f9b/1 for a live demo. Here I used SELECT * for simplicity, but you will probably have to select only the required columns for your specific need.

---

In addition, MySQL can export the query result as a CSV file , a text format that Excel, like any other spreadsheet, can read easily. Something like this can do the trick:

 SELECT * from information_schema.columns WHERE table_schema = 'db_2_66028' AND table_name = 'tbl' INTO OUTFILE '/tmp/result.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' 

Answer 3

You can export it to a comma-separated file (.csv) using a simple php script. It can be imported into Excel.

For instance:

 <?php $dbc = mysql_connect( $a, $b, $c ); $q = mysql_query( "SELECT * FROM table", $dbc ); while( $row = mysql_fetch_array( $q ) ) { echo "{$row[0]},{$row[1]},{$row[2]}\n"; } ?> 

Answer 4

This does what you want:

 SHOW FULL FIELDS FROM table