"SHOW" - Show Server Information

MySQL Tutorials - Herong's Tutorial Examples

∟"SHOW" - Show Server Information

This section provides a tutorial on how to use 'show' statements to display MySQL server information.

"SHOW" is the most commonly used administration statements on MySQL server. It provide information about databases, tables, columns, and many other information about the server.

Here is a list of SHOW statements with different options to show different information:

SHOW {BINARY MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW {REPLICAS SLAVE HOSTS}
SHOW {REPLICA SLAVE} STATUS [FOR CHANNEL channel]
SHOW [GLOBAL SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

Connect to the MySQL Server as "root" and try the following statements:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| HerongDB           |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+

mysql> use HerongDB;

mysql> show tables;

mysql> show columns from profile;
+-----------+-------------+------+-----+------------+----------------+
| Field     | Type        | Null | Key | Default    | Extra          |
+-----------+-------------+------+-----+------------+----------------+
| ID        | int(11)     | NO   | PRI | NULL       | auto_increment |
| FirstName | varchar(20) | NO   |     | NULL       |                |
| LastName  | varchar(20) | YES  |     | NULL       |                |
| Point     | double      | YES  |     | 0          |                |
| BirthDate | date        | YES  |     | 2000-12-31 |                |
| ModTime   | timestamp   | YES  |     | 2027-01-01 |                |
+-----------+-------------+------+-----+------------+----------------+

mysql> show columns from profile like '%Name';
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| FirstName | varchar(20) | NO   |     | NULL    |       |
| LastName  | varchar(20) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

mysql> show create table image;
+-------+-------------------------------------------------+
| Table | Create Table                                    |
+-------+-------------------------------------------------+
| image | CREATE TABLE `image` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Subject` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `Body` longblob,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8    |
+-------+-------------------------------------------------+

mysql> show create table image\G
*************************** 1. row ***************************
       Table: image
Create Table: CREATE TABLE `image` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Subject` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `Body` longblob,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8