Extended SHOW

Understand the extensions to the SHOW statement. Learn how to use WHERE and LIKE clauses to perform complex filtering on metadata results.

The following SHOW statements can be extended using a WHERE clause and a LIKE clause to refine the results:

• SHOW CHARACTER SET

• SHOW COLLATION

• SHOW COLUMNS

• SHOW DATABASES

• SHOW FUNCTION STATUS

• SHOW INDEX``

• SHOW OPEN TABLES

• SHOW PACKAGE STATUS

• SHOW PACKAGE BODY STATUS

• SHOW INDEX

• SHOW PROCEDURE STATUS

• SHOW STATUS

• SHOW TABLE STATUS

• SHOW TABLES

• SHOW TRIGGERS

• SHOW VARIABLES

As with a regular SELECT, the WHERE clause can be used for the specific columns returned, and the LIKE clause with the regular wildcards.

Examples

This statement shows all tables:

SHOW TABLES;
+----------------------+
| Tables_in_test       |
+----------------------+
| animal_count         |
| animals              |
| are_the_mooses_loose |
| aria_test2           |
| t1                   |
| view1                |
+----------------------+

This statement only shows tables starting with the letter 'a':

SHOW TABLES WHERE Tables_in_test LIKE 'a%';
+----------------------+
| Tables_in_test       |
+----------------------+
| animal_count         |
| animals              |
| are_the_mooses_loose |
| aria_test2           |
+----------------------+

This statement shows variables whose names start with aria and have a value greater than 8192:

SHOW VARIABLES WHERE Variable_name LIKE 'aria%' AND Value >8192;
+------------------------------+---------------------+
| Variable_name                | Value               |
+------------------------------+---------------------+
| aria_checkpoint_log_activity | 1048576             |
| aria_log_file_size           | 1073741824          |
| aria_max_sort_file_size      | 9223372036853727232 |
| aria_pagecache_buffer_size   | 134217728           |
| aria_sort_buffer_size        | 134217728           |
+------------------------------+---------------------+

_{This page is licensed: CC BY-SA / Gnu FDL}