The SHOW command is useful for exploring the details of databases, tables, indexes, and MySQL. It's a handy tool when you're writing new queries, modifying database structure, creating reports, or understanding how your MySQL server is performing. The SHOW command isn't part of the SQL standard and is MySQL-specific. It can be used in several ways:
Lists the databases that are accessible by the MySQL server. You will only see those databases that you have access to, unless you have the SHOW DATABASES privilege; privileges and user rights are discussed later in this chapter.
Shows the tables in the database, after a database has been selected with the use command.
Provides information about all tables in the current database, including the table type, number of rows, how the rows are stored, average row length, size of the datafile, next auto_increment value (if applicable), creation time, last modification time, and any extra options used with CREATE TABLE.
Shows the CREATE TABLE statement that was used to create the table tablename. The output always includes any additional information automatically added or changed by MySQL during the creation process, such as the table type and character set used.
Shows which tables the server currently has open and which tables are locked.
Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults, and other information for a table tablename. The alias DESCRIBE table produces the same output.
Presents the details of all indexes on the table tablename, including the PRIMARY KEY. It shows (amongst other information) what the attributes are that form each index, whether values in the index uniquely identify rows, how many different values there are in the index (the cardinality), and the index data structure used (usually a B-tree).
Lists the access privileges that can be given or denied to users of the version of MySQL server that you've installed. Privileges are discussed in Section 15.8.
Lists the current MySQL processes (or threads) that are running, and what query they're carrying out on which database.
Reports details of the MySQL server performance and statistics. Selected statistics and their use in database tuning is discussed later in this chapter.
Lists the possible table types that are available in the version of the MySQL server that you have installed, and notes alongside each whether you have compiled-in support for that table type. Table types are discussed in "Table Types."
Reports the values of most MySQL system variables.
Reports warnings or errors from the last command or statement that was run on a table.