What is INFORMATION SCHEMA Database

What is INFORMATION SCHEMA database? INFORMATION_SCHEMA is a database contained in MySQL / MariaDB where it has provided access to the metadata database, all information about the MySQL / MariaDB Server such as database names, tables, columns, data types, access rights and so on are contained therein.

INFORMATION_SCHEMA is very useful for a developer, because with it we can find out many things we need to build or manage databases. Before I give an example of its use, here I am a list of the tables contained in the information schema. Use this query to display:

USE INFORMATION_SCHEMA;
SHOW TABLES;

the following list is displayed

Also Read : Tips for Create MySQL/MariaDB User Database

Please explore each of the tables yourself because I haven’t explored the contents of each of these tables myself. There are several tables from INFORMATION_SCHEMA that I have used and the following are examples of utilizing INFORMATION_SCHEMA that I usually use:

# Looking for a column in any table in a database
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'columnName' AND TABLE_SCHEMA = 'databaseName';

# Displays the user and their privileges
SELECT * FROM INFORMATION_SHCEMA.USER_PRIVILEGES;

# Viewing a list of procedures in a database
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_SCHEMA
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = 'databaseName';

# Check Collation database
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'databaseName';

# Check the collation of all tables in a database
SELECT TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'databaseName';
# Check Column collation based on table
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'tableName'
AND TABLE_SCHEMA = 'databaseName';

Yes, that’s what I know about what the INFORMATION SCHEMA database is and how it’s used, that’s all the information I can provide and hopefully it’s useful. Thank you ^^.

SOURCE:

Chapter 1 INFORMATION_SCHEMA Tables