mysqlshow – Get Quick Info On MySQL DB, Table, Column and Index

by Ramesh Natarajan on August 14, 2008

MySQL Application LogoMost of the open source application uses MySQL database. To debug an issue with the open source application, it is very important to understand how to quickly get high level information about your MySQL database. In this article, I have explained how you can view the database, table, column and index information on any MySQL database using 9 examples.

In all the following mysqlshow examples, you can provide password using one of the following two methods:

  • Enter the password immediately after -p in the mysqlshow command without any space after -p. This option is helpful, if you are using mysqlshow inside a shell script.
  • Just provide option -p without any password to mysqlshow, which will prompt for a password. This option is recommended when you are using mysqlshow interactively from the command line.

1. Display available databases

Please replace tmppassword with your MySQL DB root user password.

# mysqlshow  -u root -ptmppassword

+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| sugarcrm           |
+--------------------+

2. Display all tables in a database

The example below will display all the tables located under sugarcrm database

# mysqlshow  -u root -ptmppassword sugarcrm

Database: sugarcrm
+--------------------------------+
|             Tables             |
+--------------------------------+
| accounts                       |
| accounts_audit                 |
| accounts_bugs                  |

3. Display tables along with number of columns in a database

# mysqlshow  -v -u root -p sugarcrm

Enter password:
Database: sugarcrm
+--------------------------------+----------+
|             Tables             | Columns  |
+--------------------------------+----------+
| accounts                       |       33 |
| accounts_audit                 |       10 |
| accounts_bugs                  |        5 |

4. Display total number of columns and rows of all tables in a database

Please note there are two -v in the following command.

# mysqlshow  -v -v -u root -p sugarcrm

Enter password:
Database: sugarcrm
+--------------------------------+----------+------------+
|             Tables             | Columns  | Total Rows |
+--------------------------------+----------+------------+
| accounts                       |       33 |        252 |
| accounts_audit                 |       10 |         63 |
| accounts_bugs                  |        5 |          0 |

5. Display all columns of a table

In the following example, it displays all the available column name along with additional column information for accounts table in sugarcrm database.

# mysqlshow  -u root -ptmppassword sugarcrm accounts

Database: sugarcrm  Table: accounts
+-----------------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field                       | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-----------------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id                          | char(36)     | utf8_general_ci | NO   | PRI |         |       | select,insert,update,references |         |
| name                        | varchar(150) | utf8_general_ci | YES  |     |         |       | select,insert,update,references |         |
| date_entered                | datetime     |                 | YES  |     |         |       | select,insert,update,references |         |

6. Display details about a specific column from a table

In this example, it displays information about id column from accounts table.

# mysqlshow  -u root -ptmppassword sugarcrm accounts id

Database: sugarcrm  Table: accounts  Wildcard: id
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type     | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id    | char(36) | utf8_general_ci | NO   | PRI |         |       | select,insert,update,references |         |
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+

7. Show all metadata information about a table

# mysqlshow  -i  -u root -ptmppassword sugarcrm accounts

This will display the following information about the accounts table.

  • Name
  • Engine
  • Version
  • Row_format
  • Rows
  • Avg_row_length
  • Data_length
  • Max_data_length
  • Index_length
  • Data_free
  • Auto_increment
  • Create_time
  • Update_time
  • Check_time
  • Collation
  • Checksum
  • Create_options
  • Comment

8. Display both indexes and columns of a table

Please note that the indexes are listed at the bottom of the display after the column information.

# mysqlshow -k -u root -ptmppassword sugarcrm accounts

Database: sugarcrm  Table: accounts
+-----------------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field                       | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-----------------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id                          | char(36)     | utf8_general_ci | NO   | PRI |         |       | select,insert,update,references |         |
| name                        | varchar(150) | utf8_general_ci | YES  |     |         |       | select,insert,update,references |         |
+----------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name               | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| accounts | 0          | PRIMARY                | 1            | id               | A         | 252         |          |        |      | BTREE      |         |
| accounts | 1          | idx_accnt_id_del       | 1            | id               | A         |             |          |        |      | BTREE      |         |
+----------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+

9. Display only indexes and not columns of a table

This can be done by tricking the mysqlshow, by giving an invalid column name. Since invalid_col_name doesn’t exist on accounts table, the following command will display only the indexes of accounts table.

# mysqlshow -k -u root -ptmppassword sugarcrm accounts invalid_col_name

Database: sugarcrm  Table: accounts  Wildcard: invalid_col_name
+-------+------+-----------+------+-----+---------+-------+------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+------+-----------+------+-----+---------+-------+------------+---------+
+-------+------+-----------+------+-----+---------+-------+------------+---------+
+----------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name               | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| accounts | 0          | PRIMARY                | 1            | id               | A         | 254         |          |        |      | BTREE      |         |
| accounts | 1          | idx_accnt_id_del       | 1            | id               | A         |             |          |        |      | BTREE      |         |
+----------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+

If you like this article, please bookmark it on del.icio.us and Stumble to help me out.


Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 1 comment… read it below or add one }

1 Mike J. March 12, 2009 at 11:22 am

Hi Ramesh,
I’ve having trouble understanding when you would run queries against information_schema vs. show or mysqlshow. Show command seems easier to use to pull database metadata. Can you please discuss this, and explain the difference between show and mysqlshow?
Regards,
Mike-

Leave a Comment

Previous post:

Next post: