MySQL: database table schema via describe, show, mysqldump

The MySQL database has a variety of ways to see table schema.

The table schema can be helpful for your MySQL debugging and MySQL optimization.

MySQL command "describe ..."

To see MySQL table schema in an easy to read format, you can use the MySQL command "describe ..." like this:

   mysql> describe users;
   +----------------------+--------------+------+-----+------------+----------------+
   | Field                | Type         | Null | Key | Default    | Extra          |
   +----------------------+--------------+------+-----+------------+----------------+
   | id                   | int(11)      | NO   | PRI | NULL       | auto_increment | 
   | username             | varchar(255) | YES  | MUL | NULL       |                | 
   | first_name           | varchar(255) | YES  |     | NULL       |                | 
   | last_name            | varchar(255) | YES  |     | NULL       |                | 
   +----------------------+--------------+------+-----+------------+----------------+

MySQL command "show create table ..."

To see MySQL table schema in raw SQL for one table, you can use the MySQL command "show create table ..." like this:

  mysql> show create table users;
  ...
    CREATE TABLE `users` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(255) DEFAULT NULL,
      `first_name` varchar(255) NOT NULL DEFAULT '',
      `last_name` varchar(255) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=369 DEFAULT CHARSET=latin1 
  ...

MySQL tool "mysqldump --no-data=true ..."

To see the MySQL table schema in raw SQL for an entire database, you can use the MySQL command line tool "mysqldump" like this:

  $ mysqldump --no-data=true --add-drop-table=false foo

The word "foo" is the name of the database that you want to dump.

To put these results into a file of your choice:

  $ mysqldump --no-data=true --add-drop-table=false foo > results.txt

---

What's Next?

• Index Of Articles: Web Design & Development
• MySQL: database table schema via describe, show, mysqldump
• MySQL: create random data text strings
• MySQL: create tables for joins with random rows
• MySQL: create indexes with sample query speed test
• MySQL: create indexes, foreign keys & constraints

blog comments powered by Disqus