Home | About Chris Hope | Contact Chris Hope | Search:

Listing tables and their structure with the MySQL Command Line Client

Posted May 18th, 2008 in MySql

The MySQL Command Line client allows you to run sql queries from the a command line interface. This post looks at how to show the tables in a particular database and describe their structure. This is the continuation of a series about the MySQL Command Line client. Previous posts include Using the MySQL command line tool and Running queries from the MySQL Command Line.

After logging into the MySQL command line client and selecting a database, you can list all the tables in the selected database with the following command:

mysql> show tables;

(mysql> is the command prompt, and "show tables;" is the actual query in the above example).

In a test database I have set up, this returns the following:

+----------------+
| Tables_in_test |
+----------------+
| something      |
| something_else |
+----------------+
2 rows in set (0.00 sec)

This shows us there are two tables in the database called "something" and "something_else". We can show the structure of the table using the "desc" command like so for the "something" table:

mysql> desc something;

My test database table returns a result like so, showing there are 4 columns and what types etc they are:

+--------------+------------------+------+-----+-------------------+----------------+
| Field        | Type             | Null | Key | Default           | Extra          |
+--------------+------------------+------+-----+-------------------+----------------+
| something_id | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| name         | varchar(50)      | NO   |     | NULL              |                |
| value        | varchar(50)      | NO   |     | NULL              |                |
| ts_updated   | timestamp        | YES  | MUL | CURRENT_TIMESTAMP |                |
+--------------+------------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)

Finally, you can show the indexes from a particular table like so:

mysql> show keys from something;

My test database has two indexes (these are labelled in the "key" column from the "desc something" output above as PRI and MUL). The output from the above command looks like this:

+-----------+------------+------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name   | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| something |          0 | PRIMARY    |            1 | something_id | A         |           2 |     NULL | NULL   |      | BTREE      | NULL    |
| something |          1 | ts_updated |            1 | ts_updated   | A         |        NULL |     NULL | NULL   |      | BTREE      | NULL    |
+-----------+------------+------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

Summary

The MySQL Command Line client is useful for running queries as well as displaying what tables are in a MySQL database, the structure of those tables and the indexes in those tables as covered in this post.

Comments

blog comments powered by Disqus