Debug School

rakesh kumar
rakesh kumar

Posted on

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

Display available databases
Display all tables in a database
Display tables along with number of columns in a database
Display total number of columns and rows of all tables in a database
Display all columns of a table
Display details about a specific column from a table

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
Enter fullscreen mode Exit fullscreen mode 
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| sugarcrm           |
+--------------------+
Enter fullscreen mode Exit fullscreen mode
  1. Display all tables in a database The example below will display all the tables located under sugarcrm database 
# mysqlshow  -u root -ptmppassword sugarcrm
Enter fullscreen mode Exit fullscreen mode 
Database: sugarcrm
+--------------------------------+
|             Tables             |
+--------------------------------+
| accounts                       |
| accounts_audit                 |
| accounts_bugs                  |
Enter fullscreen mode Exit fullscreen mode
  1. Display tables along with number of columns in a database 
# mysqlshow  -v -u root -p sugarcrm
Enter fullscreen mode Exit fullscreen mode 
Enter password:
Database: sugarcrm
+--------------------------------+----------+
|             Tables             | Columns  |
+--------------------------------+----------+
| accounts                       |       33 |
| accounts_audit                 |       10 |
| accounts_bugs                  |        5 |
Enter fullscreen mode Exit fullscreen mode
  1. 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 fullscreen mode Exit fullscreen mode 
Enter password:
Database: sugarcrm
+--------------------------------+----------+------------+
|             Tables             | Columns  | Total Rows |
+--------------------------------+----------+------------+
| accounts                       |       33 |        252 |
| accounts_audit                 |       10 |         63 |
| accounts_bugs                  |        5 |          0 |
Enter fullscreen mode Exit fullscreen mode
  1. 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
Enter fullscreen mode Exit fullscreen mode 
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 |         |
Enter fullscreen mode Exit fullscreen mode
  1. Display details about a specific column from a table In this example, it displays information about id column from accounts table.

Latest comments (0)