GNU Info

(mysql.info)SHOW DATABASE INFO

---

Next: SHOW TABLE STATUS Prev: SHOW Up: SHOW

---

Enter node , (file) or (file)node

Retrieving information about Database, Tables, Columns, and Indexes
...................................................................

You can use `db_name.tbl_name' as an alternative to the `tbl_name FROM
db_name' syntax.  These two statements are equivalent:

     mysql> SHOW INDEX FROM mytable FROM mydb;
     mysql> SHOW INDEX FROM mydb.mytable;

`SHOW DATABASES' lists the databases on the MySQL server host.  You can
also get this list using the `mysqlshow' command.

`SHOW TABLES' lists the tables in a given database.  You can also get
this list using the `mysqlshow db_name' command.

*NOTE:* If a user doesn't have any privileges for a table, the table
will not show up in the output from `SHOW TABLES' or `mysqlshow
db_name'.

`SHOW OPEN TABLES' lists the tables that are currently open in the
table cache. Note: Table cache.  The `Comment' field tells how many
times the table is `cached' and `in_use'.

`SHOW COLUMNS' lists the columns in a given table.  If you specify the
`FULL' option, you will also get the privileges you have for each
column.  If the column types are different than you expect them to be
based on a `CREATE TABLE' statement, note that MySQL sometimes changes
column types.  Note: Silent column changes.

The `DESCRIBE' statement provides information similar to `SHOW COLUMNS'.
Note: `DESCRIBE'.

`SHOW FIELDS' is a synonym for `SHOW COLUMNS', and `SHOW KEYS' is a
synonym for `SHOW INDEX'.  You can also list a table's columns or
indexes with `mysqlshow db_name tbl_name' or `mysqlshow -k db_name
tbl_name'.

`SHOW INDEX' returns the index information in a format that closely
resembles the `SQLStatistics' call in ODBC. The following columns are
returned:

*Column*                  *Meaning*
`Table'                   Name of the table.
`Non_unique'              0 if the index can't contain duplicates.
`Key_name'                Name of the index.
`Seq_in_index'            Column sequence number in index,
                                                 starting with 1.
`Column_name'             Column name.
`Collation'               How the column is sorted in the index.
                                                        In MySQL, this
                          can have values
                                `A' (Ascending) or `NULL' (Not
                                                      sorted).
`Cardinality'             Number of unique values in the index.
                                                       This is updated
                          by running
                           `isamchk -a'.
`Sub_part'                Number of indexed characters if the
                                                    column is only
                          partly indexed.
                                 `NULL' if the entire key is indexed.
`Comment'                 Various remarks.  For now, it tells
                                                    whether index is
                          FULLTEXT or not.

Note that as the `Cardinality' is counted based on statistics stored as
integers, it's not necessarily accurate for small tables.