The information_schema database in MySQL

information_schema is a built-in, read-only database that holds information about your other databases, such as their tables, columns and character sets. You’ll see it in phpMyAdmin, but you can’t change or delete it, and you don’t need to.

Ruachost hosting uses MariaDB 11.4, which cPanel shows as MySQL. information_schema works the same way in both.

What it contains

information_schema is made up of views, not real tables. It describes:

• The databases you have access to
• Tables and their sizes
• Columns and their data types
• Character sets and collations
• Indexes, views, triggers and stored procedures

You only see information about databases your database user has access to.

Example queries

Run these in the SQL tab in phpMyAdmin, or from the command line. Replace cpuser_dbname with your database name.

List the tables in a database:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'cpuser_dbname';

List columns and their data types for a table:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'cpuser_dbname'
  AND table_name = 'wp_posts';

Show the size of each table in megabytes:

SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'cpuser_dbname'
ORDER BY size_mb DESC;

For full details, see the MariaDB information_schema documentation.

Related articles

• Determining the size of MySQL databases and tables
• Accessing phpMyAdmin and phpPgAdmin
• Determining the MySQL version