4.4 Getting Information About Databases and Tables

What if you forget the name of a database or table, or what the
structure of a given table is (for example, what its columns are
called)? MySQL addresses this problem through several statements
that provide information about the databases and tables it
supports.

You have previously seen SHOW
DATABASES, which lists the databases managed by the
server. To find out which database is currently selected, use the
DATABASE() function:

The name of the column in the output produced by this statement is
always
Tables_in_db_name,
where db_name is the name of the
database. See Section 14.7.5.37, “SHOW TABLES Syntax”, for more information.

If you want to find out about the structure of a table, the
DESCRIBE statement is useful; it
displays information about each of a table's columns:

Field indicates the column name,
Type is the data type for the column,
NULL indicates whether the column can contain
NULL values, Key indicates
whether the column is indexed, and Default
specifies the column's default value. Extra
displays special information about columns: If a column was
created with the AUTO_INCREMENT option, the
value will be auto_increment rather than empty.

For getting information about tables in your database, the command line program mysqlshow is very helpful.

I regularly use "mysqlshow --count database_name" to quickly inventory a database; the command returns a line for every table in the named database with the number of columns and the number of rows in each table. There's no way to get all that in one command in MySQL client. Handy, eh?