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).