The SHOW query may be used in a number of forms to get information about the MySQL server, the databases, and the tables it contains. Many of these forms have an optional LIKE wild component, where wild is a string that may contain wild card characters (‘%’ for multiple characters, ‘_’ for just one) to filter the list of results. Each of the forms of the SHOW query are described below:
SHOW DATABASES [LIKE wild]
This query lists the databases that are available on the MySQL server.
SHOW [OPEN] TABLES [FROM db_name] [LIKE wild]
This query lists the tables (or, optionally, the currently OPEN tables) in the default or specified database.
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]
When FULL is not used, this query provides the same information as a DESCRIBE query (see "DESCRIBE"). The FULL option adds a listing of the privileges you have on each column to this information. SHOW FIELDS is equivalent to SHOW COLUMNS.
SHOW INDEX FROM tbl_name [FROM db_name]
This query provides detailed information about the indexes that are defined on the specified table. See the MySQL Reference manual for a guide to the results produced by this query. SHOW KEYS is equivalent to SHOW INDEX.
SHOW TABLE STATUS [FROM db_name] [LIKE wild]
This query displays detailed information about the tables in the default or specified database.
SHOW STATUS [LIKE wild]
This query displays detailed statistics for the server. See the MySQL Reference Manual for details on the meaning of each of the figures.
SHOW VARIABLES [LIKE wild]
This query lists the MySQL configuration variables and their settings. See the MySQL Reference Manual for a complete description of these options.
SHOW [FULL] PROCESSLIST
This query displays all threads running on the MySQL server, and the queries being executed by each. If you don’t have the ‘process’ privilege, you will only see threads executing your own queries. The FULL option causes the complete queries to be displayed, rather than only the first 100 characters of each (the default).
SHOW GRANTS FOR user
This query lists the GRANT queries that would be required to recreate the privileges of the specified user.
SHOW CREATE TABLE table_name
This query displays the CREATE TABLE query that would be required to reproduce the specified table.