MySQL SHOW

Introduction to MySQL SHOW

MySQL SHOW command is a special query in MySQL to view the information schema of any records stored on the database. We can define MySQL SHOW with several forms of commands that help to deliver important data records or info like logs, events created, triggers, databases, tables, columns, status information of commands, and also many more administrative level commands. This MySQL SHOW command is useful to provide various transactional, datetime, output status of all the MySQL statement queries on the MySQL server which we need to keep records of.

The MySQL SHOW commands permits to show the structure, fields, indexes, and functions created or stored procedures of the MySQL server which may be necessary to view for the users at a case of time.

Syntax:

You can find a simple basic syntax to denote the SHOW commands in MySQL like:

SHOW DATABASES;

SHOW ERRORS;

SHOW TABLES;

SHOW COLUMNS FROM TableName;

SHOW [FULL] PROCESSLIST;

SHOW EVENTS;

SHOW TABLE STATUS;

SHOW PRIVILEGES;

SHOW WARNINGS;

SHOW CREATE VIEW;

SHOW VARIABLES;

SHOW ENGINE;

SHOW INDEX;

SHOW GRANTS;

SHOW STATUS;

SHOW PLUGINS;

SHOW CREATE FUNCTION;

How SHOW Command Works in MySQL?

• Further, the MySQL SHOW Commands like SHOW CREATE VIEW query allows to display the create view MySQL statement, SHOW DATABASES query helps to list out all the databases present on the MySQL server, also we can use SHOW SCHEMAS as a synonym to be applied for SHOW DATABASES, etc. Even, we can implement a MySQL LIKE clause along with the SHOW DATABASES that indicates the list of those databases which match a certain pattern type. TO view the server status we can apply the SHOW STATUS command.
• For some critical conditions, SHOW commands in MySQL provide SHOW ERRORS query to show any errors, warnings or notes on the server. We can also look up the privileges by using SHOW PRIVILEGES command which is supported on that MySQL server version.
• Like this way, the MySQL SHOW commands works on MySQL database server to perform different activities that are responsible for different tasks.

Examples to Implement MySQL SHOW

Let us view some examples regarding the MySQL SHOW commands to understand the functionality better.

Example #1 – Using SHOW DATABASES Command

This MySQL command is responsible to display all the databases present on the server using the following statement:

Query:

SHOW DATABASES;

Output:

We can also use SHOW SCHEMAS command but it also results in the same value as above output.

Query:

SHOW SCHEMAS;

Output:

If we want to fetch the information of databases by applying a certain pattern then, we should use LIKE clause in the previous query.

Query:

SHOW DATABASES LIKE '%hu%';

Output:

Example #2 – Using SHOW TABLES Command

We will apply this MySQL SHOW command to query and retrieve tables from a specific database on the server. For this, when we log in to the MySQL server or phpMyAdmin then, we need to select a particular database to list out the tables available there using the following query:

Query:

SHOW TABLES;

Output:

The below SHOW command shows if the existing table is a base or view table in the result fetched.

Query:

SHOW FULL TABLES;

Output:

If we further want to fetch the result of tables specifying for a certain name like suppose starting or ending with particular letters, then we use the query here:

Query:

SHOW TABLES LIKE 'b%';

Output:

Example #3 – Using SHOW COLUMNS Command

This MySQL command is used to query the list of table columns found in a specific table in a particular database. We will execute the following statement:

Query:

SHOW COLUMNS FROM TableName;

Suppose, we have a sample table named ‘Books’ in a database.

Query:

SHOW COLUMNS FROM Books;

Output:

Example #4 – Using SHOW CHARACTER SET Command

We can check all the present character sets available on the MySQL server. We use the following MySQL statement for this:

Query:

SHOW CHARACTER SET;

Output:

To find a matching character set from the database we can apply the option such as LIKE clause to specify a certain pattern expression to produce the matched results. We run the below code:

Query:

SHOW CHARACTER SET LIKE 'asc%';

Output:

Example #5 – Using SHOW COLLATION Command

In MySQL, a Collation is defined as a set of procedures for relating letterings in a character set. By using this SHOW COLLATION query statement, we will get the list of collations that are supported by the MySQL server. Below is the syntax for this command.

Query:

SHOW COLLATION SET [LIKE ‘Specified_pattern’ | WHERE expression];

Also, we use LIKE clause option to show matched collations on the MySQL server, and with the help of WHERE clause we can join a conditional expression with the SHOW query.

Query:

SHOW COLLATION;

Output:

Query:

SHOW COLLATION LIKE '%bin';

Output:

Example #6 – Using SHOW ENGINE command

This MySQL SHOW query is used to display the MySQL Database Engine status report that provides us with functioning information about the storage engine on the MySQL server.

Query:

SHOW ENGINE INNODB STATUS;

Output:

We can also perform the following SHOW command:

Query:

SHOW ENGINE PERFORMANCE_SCHEMA STATUS;

Output:

Example #7 – Using SHOW ERRORS Command

In MySQL, a SHOW command is implemented to show any type of errors, notes, warnings, and notices. This SHOW command works equivalent to as SHOW WARNINGS but an exceptional case of showing errors.

Query:

SHOW ERRORS;

Output:

Query:

SHOW WARNINGS;

Output:

Also, the SHOW code below shows the number of errors found on the server.

Query:

SHOW COUNT(*) ERRORS;

Output:

We can also use the LIMIT clause with the SHOW ERRORS command to provide a specific rows number to be displayed after execution having syntax like:

Query:

SHOW ERRORS [LIMIT clause];

Example #8 – Using SHOW FUNCTION STATUS Command

If we want to view the status or characteristics of functions stored in the database including database, type, name, creation, creator, character set and revision dates, then follow to use SHOW FUNCTION command as follows:

Query:

SHOW FUNCTION STATUS;

Output:

And many more SHOW commands are available to use in different conditions.

Conclusion

• In this article, we have learnt some significant uses of different SHOW commands that exist in MySQL and are able to work for distinct operations.
• Using the SHOW syntax mentioned above, we can query to view different sections of database information from tables, columns to events, errors and functions as well as server status and logs.

Recommended Articles

This is a guide to MySQL SHOW. Here we discuss the Introduction of MySQL SHOW and the practical examples and different subquery expressions. You can also go through our suggested articles to learn more –

1. Introduction to MySQL Operators
2. Top 23 MySQL String functions
3. MySQL vs SQLite | Top 14 Comparisons
4. Guide to MySQL Timestamp