Extended SHOW

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}