In relational databases, database metadata, such as information about the MySQL server, the name of a database or table, the data type of a column, or access privileges are stored in the data dictionary and/or system catalog. MySQL's provides database metadata in a special schema called INFORMATION_SCHEMA. There is one INFORMATION_SCHEMA within each MySQL instance. It contains several read-only tables that you can query to obtain the information that you are looking for. In today's blog, we'll explore a few practical uses for the INFORMATION_SCHEMA, as demonstrated using Navicat Premium.
Obtaining Table Information
The information_schema.tables table contains metadata about, you guessed it, tables! Beside the table names, you can also retrieve their type (base table or view) and engine:
SELECT table_name, table_type, engine
FROM information_schema.tables
WHERE table_schema = 'sakila'
ORDER BY table_name;
Here is the above query and results in Navicat:
You can also query information_schema.tables to get the size of a table:
SELECT
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = "sakila"
AND table_name = "film";
Here are the results in Navicat Premium:
With a few tweaks, you can list the size of every table in every database:
SELECT
table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;
You can even use information_schema.tables to list the size of every database in a MySQL instance!
SELECT
table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;
Viewing Table Statistics
The INFORMATION_SCHEMA.STATISTICS table contains cached values. As such, these expire after 24 hours, by default. If there are no cached statistics or statistics have expired, statistics are retrieved from storage engines when querying table statistics columns.
One use for the INFORMATION_SCHEMA.STATISTICS table is to see indexes for all tables within a specific schema:
SELECT DISTINCT
TABLE_NAME,
INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';
Here are the results in Navicat for the sakila database:
You can view all indexes in all schemas by simply removing the where clause. In that case you may want to add the database name as well:
SELECT DISTINCT
stat.TABLE_SCHEMA as 'DATABASE',
TABLE_NAME,
INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS stat;
Conclusion
In today's blog we learned just a few of the many ways to use the MySQL INFORMATION_SCHEMA to obtain metadata information about a variety of objects within an MySQL instance, form the databases to tables, columns, indexes, and more. Although queries were run in Navicat Premium, Navicat for MySQL will work just as well! Try each of them for yourself; both come with a 14 day free trial!
