In MySQL, the SHOW statement is a flexible command that can be used to obtain database system metadata. It allows you to inspect several parts of the MySQL server's setup, including databases, tables, columns, users, rights, and other system data. In this article, we will learn about MySQL SHOW statement in detail with help of examples.
SHOW Statement Purpose
To access information about databases, tables, columns, users, system settings, and server status, use MySQL's SHOW statement. It lets admins keep an eye on and improve the MySQL server while offering crucial insights into the structure of your database.
Because it is read-only, the SHOW statement just obtains data from the database without altering it.
1. SHOW DATABASES
The SHOW DATABASES statement is used to list all the databases that exist within the MySQL server.
Syntax
SHOW DATABASES;
Output
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | testdb | +--------------------+
It also display system databases like mysql, performance_schema, and information_schema.
2. SHOW TABLES
The SHOW TABLES statement is used to view all the tables within the currently active database.
Syntax
SHOW TABLES;
Output
+-----------------+ | Tables_in_testdb | +-----------------+ | users | | blogs | | product_list | +-----------------+
Note: To select a database using USE dbname; before running this command. For Example: USE testdb
3. SHOW COLUMNS
The SHOW COLUMNS or DESCRIBE statement is used to get detailed information about the structure of a table, including column names, data types, default values, and whether columns can be NULL.
Syntax
SHOW COLUMNS FROM table_name;
Example
SHOW COLUMNS FROM users;
Output
+------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | INT | NO | PRI | NULL | auto_increment | | name | VARCHAR(50) | NO | | NULL | | | age | INT | YES | | NULL | | +------------+-------------+------+-----+---------+----------------+
4. SHOW INDEXES
The SHOW INDEXES statement is used to display information about the indexes on a table. This is useful for understanding the performance optimizations available through indexing.
Syntax
SHOW INDEXES FROM table_name;
Example
SHOW INDEXES FROM users;
Output
+---------+------------+----------+--------------+-------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | +---------+------------+----------+--------------+-------------+ | users | 0 | PRIMARY | 1 | id | +---------+------------+----------+--------------+-------------+
Note: Indexes improve search performance. The PRIMARY key is unique by default.
5. SHOW CREATE TABLE
The SHOW CREATE TABLE statement outputs the CREATE TABLE statement used to define a table, including all its columns, data types, constraints, and other definitions.
Syntax
SHOW CREATE TABLE table_name;
Example
SHOW CREATE TABLE users;
Output
+-----------+------------------------------------------------------+ | Table | Create Table | +-----------+------------------------------------------------------+ | users | CREATE TABLE `users` ( | | | `id` INT NOT NULL AUTO_INCREMENT, | | | `name` VARCHAR(50) NOT NULL, | | | `age` INT DEFAULT NULL, | | | PRIMARY KEY (`id`) | | | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-----------+------------------------------------------------------+
This is useful for replicating table structures.
Also read about MySQL Data Type
6. SHOW TABLE STATUS
The SHOW TABLE STATUS statement provides information about all tables in the current database, including their size, number of rows, and the storage engine used.
Syntax
SHOW TABLE STATUS;
Output
+-----------+--------+---------+------------+-------+------------+ | Name | Engine | Version | Row_format | Rows | Data_length | +-----------+--------+---------+------------+-------+------------+ | users | InnoDB | 10 | Dynamic | 1000 | 16384 | +-----------+--------+---------+------------+-------+------------+
Engine shows the storage engine (e.g., InnoDB, MyISAM).
7. SHOW VARIABLES
The SHOW VARIABLES statements displays the current system variables and their values. These system variables control various aspects of the MySQL server’s behavior and configuration.
Syntax
SHOW VARIABLES;
Filter by Specific Variable
SHOW VARIABLES LIKE 'max_connections';
Output
+-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+
Adjust system settings using SET GLOBAL variable_name = value;.
8. SHOW STATUS
The SHOW STATUS statements shows global status information about the MySQL server, such as the number of active connections, the number of queries executed, and uptime.
Syntax
SHOW STATUS;
Example
SHOW STATUS LIKE 'Threads_connected';
Output
+-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 5 | +-------------------+-------+
It helps monitor server performance.
9. SHOW GRANTS
The SHOW GRANTS statements retrieves privileges assigned to a user.
Syntax
SHOW GRANTS FOR 'username'@'host';
Example
SHOW GRANTS FOR 'admin'@'localhost';
Output
+------------------------------------------------------+ | Grants for admin@localhost | +------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' | +------------------------------------------------------+
10. SHOW PROCESSLIST
To view all of the MySQL server's running processes, use the SHOW PROCESSLIST instructions. This comprises the queries that are presently running, their current status, and the person who started them.
Syntax
SHOW PROCESSLIST;
Output
+----+------+-----------+------+---------+------+----------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------------------+------------------+ | 1 | root | localhost | test | Query | 10 | Sending data | SELECT * FROM x | +----+------+-----------+------+---------+------+----------------------+------------------+
It useful for identifying slow queries.
11. SHOW ENGINES
The SHOW ENGINES statements lists all storage engines available in the MySQL server, showing whether they are supported and the default engine in use.
Syntax
SHOW ENGINES;
Output
+--------+---------+--------------------------------------+ | Engine | Support | Comment | +--------+---------+--------------------------------------+ | InnoDB | YES | Supports transactions, row-locking | | MyISAM | YES | Non-transactional, table-locking | +--------+---------+--------------------------------------+
InnoDB supports transactions, MyISAM does not.
Conclusion
To interact with and comprehend the structure of databases, tables, and the server settings, MySQL's SHOW command is a crucial tool. It lets you examine different aspects of the database system without changing any data. The SHOW commands give you crucial information about how MySQL is set up and operating, whether you're controlling user privileges, streamlining queries, or just browsing your schema.
To ensure best speed, security, and system administration, database administrators, developers, and anybody else working with MySQL may find it essential to comprehend these SHOW statements and use them properly.
