Technology and General Blog

In variation 5., MySQL executed the Information_SCHEMA databases, which is obvious as a normal databases. Despite the fact that its conduct and operation are equivalent to a standard databases, the info contained in the database is not all common details.

Here is the very best way I can explain the MySQL Information_SCHEMA database. It is a database that contains information about other databases. It is obtainable in each MySQL occasion and is used to retail outlet metadata details about all other databases in the server. It is also identified as the technique catalog or data dictionary.

Like all the MySQL databases, the data in the Facts_SCHEMA database gets saved in study-only tables. Nevertheless, in actuality, they are views and not foundation MySQL tables. In that sense, you can not complete MySQL triggers from the tables saved in the databases, nor are there any information affiliated with them.

Notice: You will also not obtain a listing in the name of Facts_SCHEMA.

Inspite of all that, the Info_SCHEMA databases is the excellent place to question information about other databases stored on the server. This tutorial aims to deliver you with an overview of the Details_SCHEMA database and give you a couple of illustrations of utilizing the database.

Essential Information and facts about Facts_SCHEMA

As of MySQL 5. and earlier mentioned, if you execute the show databases, the info _schema databases is noticeable and can be used as a ordinary databases.

mysql> clearly show databases


| Database           |


| information_schema |

| mysql              |

| functionality_schema |

| sakila             |

| sys                |

| world              |


6 rows in set (.00 sec)

1 can use the Information and facts_SCHEMA database simply just by applying the MySQL use question as:

mysql > USE info_schema

The moment in the databases, you can see the tables stored employing the command as:

mysql> display tables


| Tables_in_info_schema          |



| Relevant_ROLES                      |

| CHARACTER_SETS                        |

| Examine_CONSTRAINTS                     |


| COLLATIONS                            |

| COLUMN_PRIVILEGES                     |

| COLUMN_Stats                     |

| COLUMNS                               |

| COLUMNS_EXTENSIONS                    |

| ENABLED_ROLES                         |

| ENGINES                               |

| EVENTS                                |

| Documents                                 |

| INNODB_BUFFER_PAGE                    |

| INNODB_BUFFER_Page_LRU                |

———–Data TRUNCATED—————-

The previously mentioned output shows some of the tables saved in the facts_schema databases. Don’t forget that they are not genuine tables and for that reason are not able to be modified. Alternatively, they are stored in the memory and dropped when the MySQL server is shut down. Upon reboot, the info_schema is rebuilt and populated with the existing details on the server.

Find from Details_schema

To look at the information saved in the details_schema database, you can use the pick statement. For example, to listing info in the engines desk, the command as:

mysql> Find * FROM facts_schema.engines

This will dump the facts saved in the desk as revealed:

Illustration 1 – Demonstrate Most significant Tables

The example beneath exhibits how to use the Facts_SCHEMA to display the greatest databases on the server.

On executing the higher than question, you will get all the tables in your dimension, setting up from the biggest to the smallest.

Below is an illustration output:

Case in point 2 – Demonstrate privileges

Employing the Desk_PRIVILEGES in the data schema database, we can get the privileges on the MySQL server. Some of the columns in this table are:

  • GRANTEE – This demonstrates the MySQL account to which the privilege is granted. This is generally in the format of [email protected]
  • Table_CATALOG – Incorporates the title of the catalog to which the desk belongs to. The value is def by default.
  • Desk_SCHEMA – The title of the databases to which the desk belongs.
  • Table_Name – The title of the table.
  • IS_GRANTEE – Boolean worth if the consumer has GRANT privilege. Usually includes a worth of Indeed or NO.

Applying the over information and facts, we can view the privileges in the MySQL server using the query:

Pick out * FROM facts_schema.SCHEMA_PRIVILEGES

This command will give you an output as shown.

Case in point 3 – Exhibit functioning procedures

We can use the PROCESSLIST table obtainable in the Information and facts_SCHEMA table to see the managing operations on the server.

In this article is an case in point question to exhibit all running procedures:

Dumping all the managing procedures will present an output very similar to the a person shown:

You can also use the command

Present Entire PROCESSLIST, which will display very similar information and facts as querying the PROCESSLIST table in the Data_SCHEMA.

Case in point 4 – Demonstrate Table Indexes Information

Applying the Figures table, we can demonstrate information about table indexes. Listed here is an example query:

Data about all the indexes in the sakila schema as shown beneath:

People are some of the illustrations that you can query from the Information and facts_SCHEMA databases.


The Information and facts_SCHEMA database is a supply of data for all other databases and the MySQL server alone. In addition, it gives you with a myriad of solutions to manage and tweak for the details to question. If you incorporate the ability of INFORNMATION_SCHEMA and the magic of SQL, you have the most electricity of any database person.

Thank you for looking through & SQL time!

Leave a Reply

Your email address will not be published. Required fields are marked *