Getting Information about Databases and Tables

MySQL provides a SHOW statement that has several variant forms that display information about databases and the tables in them. SHOW is helpful for keeping track of the contents of your databases and for reminding yourself about the structure of your tables. You can also use SHOW prior to issuing ALTER TABLE; it’s often easier to figure out how to specify a change to a column after you determine the column’s current definition.

The SHOW statement can be used to obtain information about several aspects of your databases and tables:

• List the databases managed by the server:

  SHOW DATABASES;

• List the tables in the current database or in a given database:

  SHOW TABLES;
  SHOW TABLES FROM db_name;

• Note that SHOW TABLES doesn’t show TEMPORARY tables.
• Display information about columns or indexes in a table:

  SHOW COLUMNS FROM tbl_name;
  SHOW INDEX FROM tbl_name;

• The DESCRIBE tbl_name and EXPLAIN tbl_name statements are synonymous with SHOW COLUMNS FROM tbl_name.
• Display descriptive information about tables in the current database or in a given database:

  SHOW TABLE STATUS;
  SHOW TABLE STATUS FROM db_name;

• This statement was introduced in MySQL 3.23.0.
• Display the CREATE TABLE statement that corresponds to the current structure of a table:

  SHOW CREATE TABLE tbl_name;

• This statement was introduced in MySQL 3.23.20.

Several forms of SHOW take a LIKE 'pat' clause allowing a pattern to be given that limits the scope of the output. 'pat' is interpreted as a SQL pattern that can include the ‘%‘ and ‘_‘ wildcard characters. For example, the following statement displays the names of tables in the current database that begin with 'geo':

SHOW TABLES LIKE 'geo%';

To match a literal instance of a wildcard character in a LIKE pattern, precede it with a backslash. Generally, this is done to match a literal ‘_‘, which occurs frequently in database, table, and column names.

The mysqlshow command provides some of the same information as the SHOW statement, which allows you to get database and table information from the shell:

• List databases managed by the server:

  % mysqlshow

• List tables in the named database:

  % mysqlshow db_name

• Display information about columns in the named table:

  % mysqlshow db_name tbl_name

• Display information about indexes in the named table:

  % mysqlshow --keys db_name tbl_name

• Display descriptive information about tables in the named database:

  % mysqlshow --status db_name

The mysqldump utility allows you to see the structure of your tables in the form of a CREATE TABLE statement (much like SHOW CREATE TABLE). When using mysqldump to review table structure, be sure to invoke it with the --no-data option so that you don’t get swamped with your table’s data!

% mysqldump --no-data db_name tbl_name

If you omit the table name, mysqldump displays the structure for all tables in the database.

For both mysqlshow and mysqldump, you can specify the usual connection parameter options (such as --host or --user.)

Determining Which Table Types Your Server Supports

ISAM is the only type available before MySQL 3.23. From 3.23 on, MyISAM, MERGE, and HEAP are always available, and availability of the other types can be assessed by means of an appropriate SHOW VARIABLES statement:

SHOW VARIABLES LIKE 'have_isam';
SHOW VARIABLES LIKE 'have_bdb';
SHOW VARIABLES LIKE 'have_inno%';

If the output from the query shows that the variable has a value of YES, the corresponding table handler is enabled. If the value is something else or there is no output, the handler is unavailable. The use of the pattern have_inno% to determine InnoDB availability matches both have_innodb and have_innobase. (The latter form was used in MySQL 3.23.30 to 3.23.36 before being renamed to have_innodb.)

You can use table type information to determine whether your server supports transactions. BDB and InnoDB are the two transaction-safe table types, so check whether their handlers are enabled as described in the preceding discussion.

As of MySQL 4.1, the list of table types is available directly through the SHOW TABLE TYPES statement:

mysql> SHOW TABLE TYPES;
+--------+---------+-----------------------------------------------------------+
| Type   | Support | Comment                                                   |
+--------+---------+-----------------------------------------------------------+
| MyISAM | DEFAULT | Default type from 3.23 with great performance             |
| HEAP   | YES     | Hash based, stored in memory, useful for temporary tables |
| MERGE  | YES     | Collection of identical MyISAM tables                     |
| ISAM   | YES     | Obsolete table type; Is replaced by MyISAM                |
| InnoDB | YES     | Supports transactions, row-level locking and foreign keys |
| BDB    | YES     | Supports transactions and page-level locking              |
+--------+---------+-----------------------------------------------------------+

The Support value is YES or NO to indicate that the handler is or is not available, DISABLED if the handler is present but turned off, or DEFAULT for the table type that the server uses by default. The handler designated as DEFAULT should be considered available.

Checking a Table’s Existence or Type

It’s sometimes useful to be able to tell from within an application whether or not a given table exists. You can use SHOW TABLES to find out:

SHOW TABLES LIKE 'tbl_name';
SHOW TABLES FROM db_name LIKE 'tbl_name';

If the SHOW statement lists information for the table, it exists. It’s also possible to determine table existence with either of the following statements:

SELECT COUNT(*) FROM tbl_name;
SELECT * FROM tbl_name WHERE 0;

Each statement succeeds if the table exists and fails if it doesn’t. The first statement is most appropriate for MyISAM and ISAM tables, for which COUNT(*) with no WHERE clause is highly optimized. (It’s not so good for InnoDB or BDB tables, which require a full scan to count the rows.) The second statement is more general because is runs quickly for any table type. Use of these queries is most suitable for use within application programming languages, such as Perl or PHP, because you can test the success or failure of the query and take action accordingly. They’re not especially useful in a batch script that you run from mysql because you can’t do anything if an error occurs except terminate (or ignore the error, but then there’s obviously no point in running the query at all).

To determine the type of a table, you can use SHOW TABLE STATUS as of MySQL 3.23.0 or SHOW CREATE TABLE as of MySQL 3.23.20. The output from both statements includes a table type indicator. For versions older than 3.23.0, neither statement is available; but then the only available table type is ISAM, so there is no ambiguity about what storage format your tables use.