Overview

In this tutorial, we will learn about MySQL SCHEMATA table. The SCHEMATA is a table in the INFORMATION_SCHEMA database that contains database metadata. Metadata is data about data or databases. The set of database metadata is also referred to as the catalog.

Some examples of metadata are as follows:

• Table of all tables in database.
• Table of columns in each table, type of data stored in each column, etc.

Retrieving Metadata Examples

The SCHEMATA table

mysql> DESC schemata \G
*************************** 1. row ***************************
Field: CATALOG_NAME
Type: varchar(64)
Null: YES
Key:
Default: NULL
Extra:
*************************** 2. row ***************************
Field: SCHEMA_NAME
Type: varchar(64)
Null: YES
Key:
Default: NULL
Extra:
*************************** 3. row ***************************
Field: DEFAULT_CHARACTER_SET_NAME
Type: varchar(64)
Null: NO
Key:
Default: NULL
Extra:
*************************** 4. row ***************************
Field: DEFAULT_COLLATION_NAME
Type: varchar(64)
Null: NO
Key:
Default: NULL
Extra:
*************************** 5. row ***************************
Field: SQL_PATH
Type: binary(0)
Null: YES
Key:
Default: NULL
Extra:
*************************** 6. row ***************************
Field: DEFAULT_ENCRYPTION
Type: enum(‘NO’,’YES’)
Null: NO
Key:
Default: NULL
Extra:
6 rows in set (0.00 sec)

List Databases

For example, to display the list of available databases on the MySQL Server.

mysql> SELECT Schema_Name FROM Schemata;
+——————–+
| SCHEMA_NAME |
+——————–+
| mysql |
| information_schema |
| performance_schema |
| sys |
| sakila |
| world |
+——————–+
6 rows in set (0.00 sec)

This command is equivalent to SHOW DATABASES command.

Database Information

For example, to display information about the world database, we can use the following statement:

mysql> SELECT * FROM schemata
           -> WHERE SCHEMA_NAME = ‘world’ \G

*************************** 1. row ***************************
CATALOG_NAME: def
SCHEMA_NAME: world
DEFAULT_CHARACTER_SET_NAME: utf8mb4
DEFAULT_COLLATION_NAME: utf8mb4_0900_ai_ci
SQL_PATH: NULL
DEFAULT_ENCRYPTION: NO
1 row in set (0.00 sec)

mysql>

—

MySQL Tutorials

MySQL Tutorials on this website:

https://www.testingdocs.com/mysql-tutorials-for-beginners/

For more information on MySQL Database:

https://www.mysql.com/