MySQL Tutorial

Show Database Information

SHOW and DESCRIBE Statements

Using Table-Related SHOW Statements

We can use the SHOW COLUMNS statement to lists the columns in a table, along with information about the columns.

SHOW [FULL] COLUMNS FROM <table name> [FROM <database name>] [LIKE '<value>']

FULL keyword shows more complete information about each column, and LIKE can be used to limit the values returned.

USE sakilakubili;
SHOW COLUMNS FROM film_review;
SHOW COLUMNS FROM user FROM mysql LIKE 'max%';

You can also retrieve construction information of a table by using a SHOW CREATE TABLE statement, which shows the actual table definition.

USE sakilakubili;
SHOW CREATE TABLE film_review;

Note::The entire results may not fit on the screen and what you see depends on your system.

The next statement displays a list of indexes in a table. The output SHOW INDEX statement is shown below.

USE sakilakubili;
SHOW INDEX FROM film_review;
SHOW INDEX FROM user FROM mysql;

+-------------+------------+---------+-------------+-----------
| Table       | Non_unique | Key_name| Seq_in_index| Column_name
 Collation | Cardinality | ... | Null | Index_type | Comment |
+-------------+------------+---------+-------------+-----------
| film_review |          0 | PRIMARY |            1| review_id  
A         |            0 | ... |      | BTREE      |         |
+-------------+------------+---------+-------------+-----------
1 row in set (0.00 sec)

The next statement is the SHOW TABLES statement, which displays a list of tables in the current database or a specified database.

USE sakilakubili;
SHOW TABLES;
SHOW TABLES FROM mysql LIKE 'time%';

+------------------------+
| Tables_in_sakilakubili |
+------------------------+
| film_review            |
+------------------------+
1 row in set (0.00 sec)

Using DESCRIBE Statement

DESCRIBE is another statement useful for viewing table information. The following example shows a DESCRIBE statement that returns information about all columns in the user table that end with "priv":

DESCRIBE user '%priv';

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sakila             |
| test               |
+--------------------+
4 rows in set (0.11 sec)

SHOW CREATE DATABASE sakilakubili;

As we work through this courseware, these statements will be used repeatedly to make our lives easier.

INFORMATION_SCHEMA Tables

INFORMATION_SCHEMA is the information database in MySQL server that provides access to database metadata, or data about the data, such as a table name, the constraints on a column, or access privileges. The same informationis also referred to as data dictionary and system catalog.

Here is an example to list all the tables in sakila database, in reverse alphabetical order:

SELECT table_name, table_type, engine
FROM information_schema.tables
WHERE table_schema = 'sakila'
ORDER BY table_name DESC;

+----------------------------+------------+--------+
| table_name                 | table_type | engine |
+----------------------------+------------+--------+
| actor                      | BASE TABLE | InnoDB |
| actor_info                 | VIEW       | NULL   |
| ...                        |            |  ...   |
| store                      | BASE TABLE | InnoDB |
| studio                     | BASE TABLE | InnoDB |
+----------------------------+------------+--------+
26 rows in set (0.20 sec)

Note:Users prefer to use both SHOW and INFORMATION_SCHEMA

The following example lists the various tables found in INFORMATION_SCHEMA.

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'INFORMATION_SCHEMA'
AND table_name LIKE '%';

Showing Databases

Here is a read of another table which shows that the following statements are equivalent:

SELECT SCHEMA_NAME AS `Database`
 FROM INFORMATION_SCHEMA.SCHEMATA
 [WHERE SCHEMA_NAME LIKE '
wild
']

SHOW DATABASES [LIKE '
wild
']

SELECT SCHEMA_NAME AS `Database`
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME LIKE 'sakila%';

SHOW DATABASES LIKE 'sakila%';

All material in this Show Database Information is copyright 2010 Webucator. The purpose of this website is to help you learn MySQL on your own and use of the website implies your agreement to our MySQL Tutorial Terms of Service.