MYSQL-TOOLS

Comparison of graphical tools for MySQL

Home Show Database Information

Show Database Information

In this lesson of the MySQL tutorial, you will learn...
  1. Show database, table and index information.
  2. Using INFORMATION-SCHEMA.

In this lesson of the MySQL tutorial, we learn how to see information about storage elements in MySQL.



SHOW and DESCRIBE Statements

Print PDF

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.

Syntax
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.

Code Sample:

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.

Code Sample:

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.

Code Sample:

USE sakila;
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.

Code Sample:

USE sakila;
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';

Code Sample:

SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| sakila |
| test |
+--------------------+

4 rows in set (0.11 sec)

Code Sample:

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:

Code Sample:

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.

Code Sample:

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
']

Code Sample:

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

SHOW DATABASES LIKE 'sakila%';

Show Database Information Conclusion

This lesson covered the mechanisms to create a database and some components.

To continue to learn MySQL go to the top of this page and click on the next lesson in this MySQL Tutorial's Table of Contents.
 


mysql tools