mysqlshow

You are viewing an old version of this article. View the current version here.

Shows the structure of a MariaDB database (databases, tables, columns and indexes). You can also use SHOW DATABASES, SHOW TABLES, SHOW COLUMNS, SHOW INDEX and SHOW TABLE STATUS, as well as the Information Schema tables (TABLES, COLUMNS, STATISTICS), to get similar functionality.

Usage

mysqlshow [OPTIONS] [database [table [column]]]

The output displays only the names of those databases, tables, or columns for which you have some privileges.

If no database is given then all matching databases are shown. If no table is given, then all matching tables in database are shown. If no column is given, then all matching columns and column types in table are shown.

If the last argument contains a shell or SQL wildcard (*,?,% or _) then only what's matched by the wildcard is shown. If a database name contains any underscores, those should be escaped with a backslash (some Unix shells require two) to get a list of the proper tables or columns. “*” and “?” characters are converted into SQL “%” and “_” wildcard characters. This might cause some confusion when you try to display the columns for a table with a “_” in the name, because in this case, mysqlshow shows you only the table names that match the pattern. This is easily fixed by adding an extra “%” last on the command line as a separate argument.

Options

The following options can be specified, either on the command line, or in the [mysqlshow], [client],[client-server] or [client-mariadb] groups of an option file.

OptionDescription
-c name, --character-sets-dir=nameDirectory for character set files.
--print-defaultsPrint the program argument list and exit.
--no-defaultsDon't read default options from any option file.
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--default-character-set=nameSet the default character set.
--countShow number of rows per table (may be slow for non-MyISAM tables).
-C, --compressUse compression in server/client protocol.
-# , --debug[=name]Output debug log. Often this is 'd:t:o,filename'.
--debug-checkCheck memory and open file usage at exit.
--debug-infoPrint some debug info at exit.
--default-auth=nameDefault authentication client-side plugin to use.
-?, --helpDisplay this help and exit.
-h, --host=nameConnect to host.
-i, --statusShows a lot of extra information about each table.
-k, --keysShow keys for table.
-p, --password[=name]Password to use when connecting to server. If password is not given, it's solicited on the tty.
--plugin-dir=nameDirectory for client-side plugins.
-P, --port=# Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306).
--protocol=nameThe protocol to use for connection (tcp, socket, pipe, memory).
-t, --show-table-typeShow table type column.
-S, --socket=nameThe socket file to use for connection.
--sslEnable SSL for connection (automatically enabled with other flags).
--ssl-ca=nameCA file in PEM format (check OpenSSL docs, implies --ssl).
--ssl-capath=nameCA directory (check OpenSSL docs, implies --ssl).
--ssl-cert=nameX509 cert in PEM format (implies --ssl).
--ssl-cipher=nameSSL cipher to use (implies --ssl).
--ssl-key=nameX509 key in PEM format (implies --ssl).
--ssl-crl=nameCertificate revocation list (implies --ssl).
--ssl-crlpath=nameCertificate revocation list path (implies --ssl).
--ssl-verify-server-certVerify server's "Common Name" in its cert against hostname used when connecting. This option is disabled by default.
-u, --user=nameUser for login if not current user.
-v, --verboseMore verbose output; you can use this multiple times to get even more verbose output.
-V, --versionOutput version information and exit.

Examples

Getting a list of databases:

bin/mysqlshow
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| test               |
+--------------------+

Getting a list of tables in the test database:

bin/mysqlshow test
Database: test
+---------+
| Tables  |
+---------+
| author  |
| book    |
| city    |
| country |
+---------+

Getting a list of columns in the test.book table:

bin/mysqlshow test book
Database: test  Table: book
+-----------+-----------------------+-------------------+------+-----+---------+----------------+--------------------------------+---------+
| Field     | Type                  | Collation         | Null | Key | Default | Extra          | Privileges                      | Comment |
+-----------+-----------------------+-------------------+------+-----+---------+----------------+--------------------------------+---------+
| id        | mediumint(8) unsigned |                   | NO   | PRI |         | auto_increment | select,insert,update,references |         |
| title     | varchar(200)          | latin1_swedish_ci | NO   |     |         |                | select,insert,update,references |         |
| author_id | smallint(5) unsigned  |                   | NO   | MUL |         |                | select,insert,update,references |         |
+-----------+-----------------------+-------------------+------+-----+---------+----------------+--------------------------------+---------+

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.