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:
- SHOW DATABASES
-
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.
- SHOW TABLES
-
Shows the tables in the database, after a database has been selected with the use command.
- SHOW TABLE STATUS
-
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.
- SHOW CREATE TABLE tablename
-
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.
- SHOW OPEN TABLES
-
Shows which tables the server currently has open and which tables are locked.
- SHOW COLUMNS FROM tablename
-
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.
- SHOW INDEX FROM tablename
-
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).
- SHOW PRIVILEGES
-
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.
- SHOW PROCESSLIST
-
Lists the current MySQL processes (or threads) that are running, and what query they're carrying out on which database.
- SHOW STATUS
-
Reports details of the MySQL server performance and statistics. Selected statistics and their use in database tuning is discussed later in this chapter.
- SHOW TABLE TYPES
-
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."
- SHOW VARIABLES
-
Reports the values of most MySQL system variables.
- SHOW WARNING and SHOW ERRORS
-
Reports warnings or errors from the last command or statement that was run on a table. |