MySQL Metadata

Overview

Metadata is data about data. MySQL provides extensive metadata through system databases and information schema tables, allowing you to query database structure, table definitions, indexes, and more.

Metadata Sources

information_schema: Metadata about all databases
mysql: System database with grants
performance_schema: Performance metrics
sys: Views on performance_schema
SHOW Commands: Display metadata directly

information_schema Database

Overview

sql

-- Access information_schema
USE information_schema;

-- List all information_schema tables
SHOW TABLES;

-- Key information_schema tables
-- SCHEMATA: Database information
-- TABLES: Table information
-- COLUMNS: Column information
-- KEY_COLUMN_USAGE: Key usage
-- STATISTICS: Index information
-- USER_PRIVILEGES: User privileges
-- TABLE_CONSTRAINTS: Constraints

Database Metadata

sql

-- List all databases
SELECT schema_name AS database_name
FROM information_schema.SCHEMATA
WHERE schema_name NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

-- Database character sets
SELECT 
    schema_name,
    default_character_set_name,
    default_collation_name
FROM information_schema.SCHEMATA;

-- Database sizes
SELECT 
    table_schema AS database_name,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY size_mb DESC;

Table Metadata

sql

-- List tables in current database
SELECT 
    table_name,
    table_type,
    engine,
    row_format,
    table_rows,
    avg_row_length,
    data_length,
    index_length,
    auto_increment,
    create_time,
    update_time,
    check_time,
    table_collation
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
ORDER BY table_name;

-- Filter by engine
SELECT table_name
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
  AND engine = 'InnoDB';

-- Filter by table type
SELECT table_name, table_type
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
  AND table_type = 'BASE TABLE';

Column Metadata

sql

-- Get table columns
SELECT 
    column_name,
    ordinal_position,
    column_default,
    is_nullable,
    data_type,
    character_maximum_length,
    numeric_precision,
    numeric_scale,
    character_set_name,
    collation_name,
    column_type,
    column_key,
    extra
FROM information_schema.COLUMNS
WHERE table_schema = DATABASE()
  AND table_name = 'users'
ORDER BY ordinal_position;

-- Find nullable columns
SELECT table_name, column_name, is_nullable
FROM information_schema.COLUMNS
WHERE table_schema = DATABASE()
  AND is_nullable = 'YES';

-- Find auto increment columns
SELECT table_name, column_name
FROM information_schema.COLUMNS
WHERE table_schema = DATABASE()
  AND extra LIKE '%auto_increment%';

Index Metadata

sql

-- List indexes
SELECT 
    index_name,
    column_name,
    seq_in_index,
    cardinality,
    index_type,
    is_unique
FROM information_schema.STATISTICS
WHERE table_schema = DATABASE()
  AND table_name = 'users'
ORDER BY index_name, seq_in_index;

-- Index details
SELECT 
    s.table_name,
    s.index_name,
    s.column_name,
    s.cardinality,
    t.table_rows,
    ROUND(s.cardinality / t.table_rows * 100, 2) AS selectivity_pct
FROM information_schema.STATISTICS s
JOIN information_schema.TABLES t 
    ON s.table_name = t.table_name 
    AND s.table_schema = t.table_schema
WHERE s.table_schema = DATABASE()
ORDER BY s.index_name, s.seq_in_index;

-- Unique indexes
SELECT DISTINCT index_name, column_name
FROM information_schema.STATISTICS
WHERE table_schema = DATABASE()
  AND table_name = 'users'
  AND non_unique = 0;

SHOW Commands

Database SHOW Commands

sql

-- Show databases
SHOW DATABASES;
SHOW DATABASES LIKE 'myapp%';
SHOW DATABASES WHERE schema_name LIKE '%dev%';

-- Show current database
SELECT DATABASE();

-- Show character sets
SHOW CHARACTER SET;
SHOW CHARACTER SET LIKE 'utf8%';

-- Show collations
SHOW COLLATION;
SHOW COLLATION LIKE 'utf8mb4%';

-- Show engines
SHOW ENGINES;
SHOW STORAGE ENGINES;

Table SHOW Commands

sql

-- Show tables
SHOW TABLES;
SHOW TABLES FROM database_name;
SHOW TABLES LIKE 'user%';
SHOW FULL TABLES;  -- Includes table type

-- Show table structure
DESCRIBE table_name;
DESC table_name;
EXPLAIN table_name;
SHOW COLUMNS FROM table_name;
SHOW FIELDS FROM table_name;

-- Show create statement
SHOW CREATE TABLE table_name;
SHOW CREATE TABLE table_name\G;

-- Show table status
SHOW TABLE STATUS;
SHOW TABLE STATUS LIKE 'users%';
SHOW TABLE STATUS WHERE name = 'users';

Index SHOW Commands

sql

-- Show indexes
SHOW INDEX FROM table_name;
SHOW INDEXES FROM table_name;
SHOW KEYS FROM table_name;

-- Show index details
SHOW INDEX FROM users\G;

Other SHOW Commands

sql

-- Show users
SHOW GRANTS FOR CURRENT_USER();
SHOW GRANTS FOR 'username'@'hostname';

-- Show privileges
SHOW PRIVILEGES;

-- Show processes
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- Show variables
SHOW VARIABLES;
SHOW VARIABLES LIKE 'innodb%';
SHOW GLOBAL VARIABLES LIKE 'max%';

-- Show status
SHOW STATUS;
SHOW STATUS LIKE 'Connections%';
SHOW GLOBAL STATUS;

-- Show engines
SHOW ENGINES;

MySQL System Database

mysql Database

sql

-- Use mysql database
USE mysql;

-- User information
SELECT user, host FROM mysql.user;

-- Database privileges
SELECT * FROM mysql.db;

-- Table privileges
SELECT * FROM mysql.tables_priv;

-- Column privileges
SELECT * FROM mysql.columns_priv;

-- Proxies
SELECT * FROM mysql.proxies_priv;

User Metadata

sql

-- List all users
SELECT user, host FROM mysql.user;

-- User with all hosts
SELECT DISTINCT user FROM mysql.user;

-- User privileges
SELECT * FROM mysql.user WHERE user = 'root';

-- Password hashes
SELECT user, host, plugin, authentication_string
FROM mysql.user;

Performance Metadata

performance_schema Database

sql

-- Use performance_schema
USE performance_schema;

-- Show available instruments
SELECT * FROM setup_instruments LIMIT 10;

-- Enable instruments
UPDATE setup_instruments 
SET enabled = 'YES', timed = 'YES'
WHERE name LIKE 'statement/%';

-- Show consumers
SELECT * FROM setup_consumers;

Performance Queries

sql

-- Wait events
SELECT * FROM events_waits_current
LIMIT 10;

-- Statement performance
SELECT 
    digest_text AS statement,
    count_star AS exec_count,
    avg_timer_wait / 1000000000000 AS avg_wait_ms
FROM events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- Table I/O
SELECT 
    object_name AS table_name,
    count_read,
    count_write,
    sum_timer_read / 1000000000000 AS read_time_ms
FROM file_summary_by_instance
WHERE object_name IS NOT NULL
ORDER BY sum_timer_read DESC
LIMIT 10;

sys Database

sql

-- Use sys database (MySQL 5.7+)
USE sys;

-- User statistics
SELECT * FROM sys.user_summary;

-- Table statistics
SELECT * FROM sys.schema_unused_tables;
SELECT * FROM sys.schema_redundant_indexes;

-- Wait analysis
SELECT * FROM sys.wait_classes_global_by_latency;
SELECT * FROM sys.waits_global_by_latency;

-- Memory usage
SELECT * FROM sys.memory_global_by_current_bytes;

Metadata Queries

Search for Tables

sql

-- Find tables with specific column
SELECT table_name
FROM information_schema.COLUMNS
WHERE table_schema = DATABASE()
  AND column_name = 'user_id';

-- Find tables without primary key
SELECT t.table_name
FROM information_schema.TABLES t
LEFT JOIN information_schema.KEY_COLUMN_USAGE k 
    ON t.table_name = k.table_name
    AND t.table_schema = k.table_schema
WHERE t.table_schema = DATABASE()
  AND k.constraint_name = 'PRIMARY';

-- Find tables with specific engine
SELECT table_name
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
  AND engine = 'InnoDB';

Search for Columns

sql

-- Find all columns named 'email'
SELECT 
    table_name,
    column_name,
    data_type
FROM information_schema.COLUMNS
WHERE table_schema = DATABASE()
  AND column_name = 'email';

-- Find all VARCHAR columns
SELECT table_name, column_name, character_maximum_length
FROM information_schema.COLUMNS
WHERE table_schema = DATABASE()
  AND data_type = 'varchar';

-- Find all datetime columns
SELECT table_name, column_name
FROM information_schema.COLUMNS
WHERE table_schema = DATABASE()
  AND data_type IN ('date', 'datetime', 'timestamp');

Search for Constraints

sql

-- Find foreign keys
SELECT 
    table_name,
    column_name,
    referenced_table_name,
    referenced_column_name
FROM information_schema.KEY_COLUMN_USAGE
WHERE table_schema = DATABASE()
  AND referenced_table_schema IS NOT NULL;

-- Find unique constraints
SELECT 
    constraint_name,
    table_name,
    column_name
FROM information_schema.TABLE_CONSTRAINTS c
JOIN information_schema.KEY_COLUMN_USAGE k 
    ON c.constraint_name = k.constraint_name
WHERE c.table_schema = DATABASE()
  AND c.constraint_type = 'UNIQUE';

Metadata Functions

Database Information Functions

sql

-- Current database
SELECT DATABASE();
SELECT SCHEMA();

-- Current user
SELECT USER();
SELECT CURRENT_USER();
SELECT SESSION_USER();

-- Connection ID
SELECT CONNECTION_ID();

-- Last insert ID
SELECT LAST_INSERT_ID();

Table Information Functions

sql

-- Row count
SELECT ROW_COUNT();

-- Auto increment value
SELECT @@auto_increment_increment;

-- Check table existence
SELECT COUNT(*) 
FROM information_schema.TABLES 
WHERE table_schema = DATABASE()
  AND table_name = 'users';

Practical Examples

Database Documentation

sql

-- Generate table documentation
SELECT 
    t.table_name,
    t.table_comment,
    t.engine,
    c.column_name,
    c.data_type,
    c.is_nullable,
    c.column_default,
    c.column_comment
FROM information_schema.TABLES t
LEFT JOIN information_schema.COLUMNS c 
    ON t.table_name = c.table_name 
    AND t.table_schema = c.table_schema
WHERE t.table_schema = DATABASE()
ORDER BY t.table_name, c.ordinal_position;

Schema Comparison

sql

-- Compare tables between databases
SELECT 
    COALESCE(a.table_name, b.table_name) AS table_name,
    CASE 
        WHEN a.table_name IS NULL THEN 'Only in B'
        WHEN b.table_name IS NULL THEN 'Only in A'
        ELSE 'In both'
    END AS status
FROM (
    SELECT table_name FROM information_schema.TABLES 
    WHERE table_schema = 'database_a'
) a
FULL OUTER JOIN (
    SELECT table_name FROM information_schema.TABLES 
    WHERE table_schema = 'database_b'
) b ON a.table_name = b.table_name;

Unused Indexes

sql

-- Find potentially unused indexes
SELECT 
    t.table_name,
    s.index_name,
    s.column_name,
    s.cardinality,
    t.table_rows
FROM information_schema.STATISTICS s
JOIN information_schema.TABLES t 
    ON s.table_name = t.table_name
WHERE s.table_schema = DATABASE()
  AND s.index_name != 'PRIMARY'
  AND s.cardinality IS NULL;

Foreign Key Report

sql

-- Generate foreign key documentation
SELECT 
    kcu.table_name AS child_table,
    kcu.column_name AS child_column,
    kcu.referenced_table_name AS parent_table,
    kcu.referenced_column_name AS parent_column,
    rc.update_rule,
    rc.delete_rule
FROM information_schema.KEY_COLUMN_USAGE kcu
JOIN information_schema.REFERENTIAL_CONSTRAINTS rc 
    ON kcu.constraint_name = rc.constraint_name
WHERE kcu.table_schema = DATABASE()
ORDER BY kcu.table_name, kcu.constraint_name;

Summary

MySQL metadata provides:

Database Information: Names, sizes, engines
Table Information: Columns, rows, indexes
Column Information: Types, defaults, constraints
Index Information: Keys, cardinality, uniqueness
Performance Data: Queries, waits, I
**User

Use metadata for database administration, documentation, optimization, and monitoring.

Previous: Clone Tables

Next: Sequences

MySQL Metadata ​

Overview ​

Metadata Sources ​

information_schema Database ​

Overview ​

Database Metadata ​

Table Metadata ​

Column Metadata ​

Index Metadata ​

SHOW Commands ​

Database SHOW Commands ​

Table SHOW Commands ​

Index SHOW Commands ​

Other SHOW Commands ​

MySQL System Database ​

mysql Database ​

User Metadata ​

Performance Metadata ​

performance_schema Database ​

Performance Queries ​

sys Database ​

Metadata Queries ​

Search for Tables ​

Search for Columns ​

Search for Constraints ​

Metadata Functions ​

Database Information Functions ​

Table Information Functions ​

Practical Examples ​

Database Documentation ​

Schema Comparison ​

Unused Indexes ​

Foreign Key Report ​

Summary ​

MySQL Metadata

Overview

Metadata Sources

information_schema Database

Overview

Database Metadata

Table Metadata

Column Metadata

Index Metadata

SHOW Commands

Database SHOW Commands

Table SHOW Commands

Index SHOW Commands

Other SHOW Commands

MySQL System Database

mysql Database

User Metadata

Performance Metadata

performance_schema Database

Performance Queries

sys Database

Metadata Queries

Search for Tables

Search for Columns

Search for Constraints

Metadata Functions

Database Information Functions

Table Information Functions

Practical Examples

Database Documentation

Schema Comparison

Unused Indexes

Foreign Key Report

Summary