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

Most 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 | |
+———-+————+————————+————–+——————+———–+————-+———-+——–+——+————+———+