SHOW

mysql

SHOW COLUMNS

SHOW COLUMNS FROM City;
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern' | WHERE expr]

SHOW COLUMNS displays information about the columns in a given table. It also works for views. The LIKE clause, if present, indicates which column names to match. The WHERE clause can be given to select rows using more general conditions. The FULL keyword causes the output to include the column collation and comments, as well as the privileges you have for each column.

SHOW INDEX FROM tbl_name returns table index information. SHOW KEYS is a synonym for SHOW INDEX.
SHOW CREATE TABLE tb_name shows the CREATE TABLE statement that creates the given table. This statement also works with views.
SHOW TRIGGERS lists the triggers currently defined for tables in a database.
SHOW CREATE TRIGGER trigger_name shows a CREATE TRIGGER statement that creates the given trigger.
SHOW CREATE VIEW view_name shows a CREATE VIEW statement that creates the given view.
SHOW PRIVILEGES shows the list of system privileges for use with GRANT.
SHOW GRANTS [FOR user] lists the GRANT statement or statements that must be issued to duplicate the privileges that are granted to a MySQL user account. SHOW GRANTS displays only the privileges granted explicitly to the named account. Other privileges might be available to the account, but they are not displayed. For example, if an anonymous account exists, the named account might be able to use its privileges, but SHOW GRANTS will not display them. SHOW GRANTS requires the SELECT privilege for the mysql database.
SHOW VARIABLES;
SHOW STATUS

SHOW WARNINGS

SHOW WARNINGS [LIMIT [offset,] row_count]
SHOW COUNT(*) WARNINGS

SHOW WARNINGS shows the error, warning, and note messages that resulted from the last statement that generated messages. It shows nothing if the last statement used a table and generated no messages. (That is, a statement that uses a table but generates no messages clears the message list.) Statements that do not use tables and do not generate messages have no effect on the message list. The value of warning_count might be greater than the number of messages displayed by SHOW WARNINGS if the max_error_count system variable is set so low that not all messages are stored.

SHOW ERRORS

SHOW ERRORS [LIMIT [offset,] row_count]
SHOW COUNT(*) ERRORS

Displays errors. The SHOW COUNT(*) ERRORS statement displays the number of errors. You can also retrieve this number from the error_count variable:
SHOW COUNT(*) ERRORS;
SELECT @@error_count;

SHOW [FULL] PROCESSLIST shows you which threads are running. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads. This statement is very useful if you get the “too many connections” error message and want to find out what is going on. MySQL reserves one extra connection to be used by accounts that have the SUPER privilege, to ensure that administrators should always be able to connect and check the system (assuming that you are not giving this privilege to all your users). Threads can be killed with the KILL statement.

SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern' | WHERE expr] provides server status information. This information also can be obtained using the mysqladmin extended-status command.

With the GLOBAL modifier, SHOW STATUS displays the status values for all connections to MySQL. With SESSION, it displays the status values for the current connection. If no modifier is present, the default is SESSION. LOCAL is a synonym for SESSION.

Some status variables have only a global value. For these, you get the same value for both GLOBAL and SESSION.

SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern' | WHERE expr] works likes SHOW TABLES, but provides a lot of information about each non-TEMPORARY table. You can also get this list using the mysqlshow —status db_name command. The LIKE clause, if present, indicates which table names to match. The WHERE clause can be given to select rows using more general conditions.

SHOW [FULL] TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr] lists the non-TEMPORARY tables in a given database. You can also get this list using the mysqlshow db_name command. The LIKE clause, if present, indicates which table names to match. The WHERE clause can be given to select rows using more general conditions. This statement also lists any views in the database. The FULL modifier is supported such that SHOW FULL TABLES displays a second output column. Values for the second column are BASE TABLE for a table and VIEW for a view.

SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern' | WHERE expr] shows the values of MySQL system variables. This information also can be obtained using the mysqladmin variables command. With the GLOBAL modifier, SHOW VARIABLES displays the values that are used for new connections to MySQL. With SESSION, it displays the values that are in effect for the current connection. If no modifier is present, the default is SESSION. LOCAL is a synonym for SESSION.

SHOW OPEN TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr] lists the non-TEMPORARY tables that are currently open in the table cache.

SHOW CHARACTER SET

SHOW CHARACTER SET;
SHOW CHARACTER SET LIKE 'latin%';
SHOW CHARACTER SET WHERE `Default collation` LIKE '%japanese%';

The Maxlen column shows the maximum number of bytes required to store one character.

SHOW COLLATION

SHOW COLLATION;
SHOW COLLATION LIKE 'latin1%';

The Default column indicates whether a collation is the default for its character set. Compiled indicates whether the character set is compiled into the server. Sortlen is related to the amount of memory required to sort strings expressed in the character set.

SHOW CREATE DATABASE

SHOW CREATE DATABASE db_name
SHOW CREATE SCHEMA db_name;

Shows the CREATE DATABASE statement that creates the given database. SHOW CREATE SCHEMA is a synonym for SHOW CREATE DATABASE.

SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

These statements return the exact string that can be used to re-create the named routine. The statements require that you be the owner of the routine or have SELECT access to the mysql.proc table. character_set_client is the session value of the character_set_client system variable when the routine was created. collation_connection is the session value of the collation_connection system variable when the routine was created. Database Collation is the collation of the database with which the routine is associated.

SHOW DATABASES

SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]

SHOW DATABASES lists the databases on the MySQL server host. SHOW SCHEMAS is a synonym for SHOW DATABASES. The LIKE clause, if present, indicates which database names to match. The WHERE clause can be given to select rows using more general conditions. You see only those databases for which you have some kind of privilege, unless you have the global SHOW DATABASES privilege. If the server was started with the —skip-show-database option, you cannot use this statement at all unless you have the SHOW DATABASES privilege.

SHOW ENGINES displays a list of storage engines, useful for checking whether a storage engine is supported, or to see what the default engine is.

SHOW ENGINE

SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW ENGINE INNODB STATUS;
SHOW ENGINE INNODB MUTEX;
SHOW ENGINE {NDB | NDBCLUSTER} STATUS;

SHOW SLAVE HOSTS displays a list of replication slaves currently registered with the master. Only slaves started with the —report-host=host_name option are visible in this list. The list is displayed on any server (not just the master server).

SHOW SLAVE STATUS provides status information on essential parameters of the slave threads. If you issue this statement using the mysql client, you can use a \G statement terminator rather than a semicolon to obtain a more readable vertical layout.

SHOW BINARY LOGS and SHOW MASTER LOGS statements are used as part of the procedure described in “PURGE BINARY LOGS Syntax”, that shows how to determine which logs can be purged. SHOW MASTER LOGS is equivalent to SHOW BINARY LOGS.

SHOW MASTER STATUS provides status information about the binary log files of the master.
SHOW BINLOG EVENTS

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Shows the events in the binary log. Issuing a SHOW BINLOG EVENTS with no LIMIT clause could start a very time- and resource-consuming process because the server returns to the client the complete contents of the binary log. As an alternative to SHOW BINLOG EVENTS, use the mysqlbinlog utility to save the binary log to a text file for later examination and analysis.

http://dev.mysql.com/doc/refman/5.1/en/show-binlog-events.html
http://dev.mysql.com/doc/refman/5.1/en/mysqlbinlog.html

SHOW PLUGINS displays information about known plugins.
SHOW PROCEDURE CODE and SHOW FUNCTION CODE
These statements are MySQL extensions that are available only for servers that have been built with debugging support. They display a representation of the internal implementation of the named routine. The statements require that you be the owner of the routine or have SELECT access to the mysql.proc table.

If the named routine is available, each statement produces a result set. Each row in the result set corresponds to one “instruction” in the routine. The first column is Pos, which is an ordinal number beginning with 0. The second column is Instruction, which contains an SQL statement (usually changed from the original source), or a directive which has meaning only to the stored-routine handler.

SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern' | WHERE expr]

These statements are MySQL extensions. They return characteristics of routines, such as the database, name, type, creator, creation and modification dates, and character set information. The LIKE clause, if present, indicates which procedure or function names to match. The WHERE clause can be given to select rows using more general conditions. character_set_client is the session value of the character_set_client system variable when the routine was created. collation_connection is the session value of the collation_connection system variable when the routine was created. Database Collation is the collation of the database with which the routine is associated.

SHOW PROFILES and SHOW PROFILE statements display profiling information that indicates resource usage for statements executed during the course of the current session. Profiling is controlled by the profiling session variable, which has a default value of 0 (OFF). Profiling is enabled by setting profiling to 1 or ON:

SET profiling = 1;

SHOW PROFILES displays a list of the most recent statements sent to the master. The size of the list is controlled by the profiling_history_size session variable, which has a default value of 15. The maximum value is 100. Setting the value to 0 has the practical effect of disabling profiling. All statements are profiled except SHOW PROFILES and SHOW PROFILE, so you will find neither of those statements in the profile list. SHOW PROFILE displays detailed information about a single statement. Without the FOR QUERY n clause, the output pertains to the most recently executed statement. If FOR QUERY n is included, SHOW PROFILE displays information for statement n. The values of n correspond to the Query_ID values displayed by SHOW PROFILES.

The LIMIT row_count clause may be given to limit the output to row_count rows. If LIMIT is given, OFFSET offset may be added to begin the output offset rows into the full set of rows. By default, SHOW PROFILE displays Status and Duration columns. The Status values are like the State values displayed by SHOW PROCESSLIST, althought there might be some minor differences in interpretion for the two statements for some status values. Optional type values may be specified to display specific additional types of information:

  1. ALL displays all information
  2. BLOCK IO displays counts for block input and output operations
  3. CONTEXT SWITCHES displays counts for voluntary and involuntary context switches
  4. CPU displays user and system CPU usage times
  5. IPC displays counts for messages sent and received
  6. MEMORY is not currently implemented
  7. PAGE FAULTS displays counts for major and minor page faults
  8. SOURCE displays the names of functions from the source code, together with the name and line number of the file in which the function occurs
  9. SWAPS displays swap counts

Profiling is enabled per session. When a session ends, its profiling information is lost.

SELECT @@profiling;
SET profiling = 1;
DROP TABLE IF EXISTS t1;
CREATE TABLE T1 (id INT);
SHOW PROFILES;
SHOW PROFILE CPU FOR QUERY 2;

Profiling is only partially functional on some architectures. For values that depend on the getrusage() system call, NULL is returned on systems that do not support the call.

SHOW CONTRIBUTORS and SHOW AUTHORS display information on people who contribute to MySQL.

Other SHOW commands

SHOW CREATE EVENT;
SHOW EVENT;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License