MySQL & Database Design

Information Schema & Metadata

13 min Lesson 25 of 40

Information Schema & Metadata

The Information Schema is a special database that provides access to database metadata - information about your database structure, tables, columns, indexes, and more. It's incredibly useful for database introspection, generating documentation, and building dynamic database tools. In this lesson, we'll explore how to query and utilize this powerful resource.

What is Information Schema?

Information Schema is a read-only database that MySQL automatically maintains. It contains metadata about all other databases, tables, columns, privileges, and more.

Key Concept: Information Schema uses standardized ANSI SQL views, making your queries portable across different database systems (with minor adjustments).

Accessing Information Schema

-- List all databases
SELECT SCHEMA_NAME
FROM information_schema.SCHEMATA
ORDER BY SCHEMA_NAME;

-- Get database size
SELECT
    SCHEMA_NAME AS 'Database',
    ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.TABLES
GROUP BY SCHEMA_NAME
ORDER BY SUM(DATA_LENGTH + INDEX_LENGTH) DESC;

-- Show all Information Schema tables
SHOW TABLES FROM information_schema;

Querying Table Information

Get detailed information about tables in your database:

-- List all tables in a specific database
SELECT
    TABLE_NAME,
    TABLE_TYPE,
    ENGINE,
    TABLE_ROWS,
    AVG_ROW_LENGTH,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size (MB)',
    TABLE_COLLATION,
    CREATE_TIME,
    UPDATE_TIME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

-- Find largest tables
SELECT
    TABLE_NAME,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size (MB)',
    TABLE_ROWS,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / TABLE_ROWS, 2) AS 'Bytes per Row'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
AND TABLE_ROWS > 0
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC
LIMIT 10;

-- Find tables without primary key
SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME NOT IN (
    SELECT TABLE_NAME
    FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND TABLE_SCHEMA = 'your_database'
);

Column Information

-- List all columns in a table
SELECT
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_KEY,
    COLUMN_DEFAULT,
    EXTRA,
    COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
AND TABLE_NAME = 'customers'
ORDER BY ORDINAL_POSITION;

-- Find all columns with specific data type
SELECT
    TABLE_NAME,
    COLUMN_NAME,
    COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
AND DATA_TYPE = 'varchar'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- Find all TEXT/BLOB columns (memory intensive)
SELECT
    TABLE_NAME,
    COLUMN_NAME,
    COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
AND DATA_TYPE IN ('text', 'mediumtext', 'longtext', 'blob', 'mediumblob', 'longblob')
ORDER BY TABLE_NAME;

-- Find columns with NULL values
SELECT
    TABLE_NAME,
    COLUMN_NAME,
    IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
AND IS_NULLABLE = 'YES'
AND COLUMN_KEY != 'PRI'
ORDER BY TABLE_NAME, COLUMN_NAME;

Index Information

-- Show all indexes in a database
SELECT
    TABLE_NAME,
    INDEX_NAME,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS COLUMNS,
    INDEX_TYPE,
    NON_UNIQUE,
    NULLABLE,
    COMMENT
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
GROUP BY TABLE_NAME, INDEX_NAME, INDEX_TYPE, NON_UNIQUE, NULLABLE, COMMENT
ORDER BY TABLE_NAME, INDEX_NAME;

-- Find duplicate indexes
SELECT
    s1.TABLE_NAME,
    s1.INDEX_NAME AS Index1,
    s2.INDEX_NAME AS Index2,
    GROUP_CONCAT(s1.COLUMN_NAME ORDER BY s1.SEQ_IN_INDEX) AS Columns
FROM information_schema.STATISTICS s1
JOIN information_schema.STATISTICS s2
    ON s1.TABLE_SCHEMA = s2.TABLE_SCHEMA
    AND s1.TABLE_NAME = s2.TABLE_NAME
    AND s1.SEQ_IN_INDEX = s2.SEQ_IN_INDEX
    AND s1.COLUMN_NAME = s2.COLUMN_NAME
    AND s1.INDEX_NAME < s2.INDEX_NAME
WHERE s1.TABLE_SCHEMA = 'your_database'
GROUP BY s1.TABLE_NAME, s1.INDEX_NAME, s2.INDEX_NAME
HAVING COUNT(*) = (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = s1.TABLE_SCHEMA
    AND TABLE_NAME = s1.TABLE_NAME
    AND INDEX_NAME = s1.INDEX_NAME
);

-- Find tables without indexes
SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME NOT IN (
    SELECT DISTINCT TABLE_NAME
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = 'your_database'
);

Constraint Information

-- Show all foreign keys
SELECT
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_database'
AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME, CONSTRAINT_NAME;

-- Get detailed constraint information
SELECT
    tc.CONSTRAINT_NAME,
    tc.TABLE_NAME,
    tc.CONSTRAINT_TYPE,
    kcu.COLUMN_NAME,
    rc.UPDATE_RULE,
    rc.DELETE_RULE
FROM information_schema.TABLE_CONSTRAINTS tc
LEFT JOIN information_schema.KEY_COLUMN_USAGE kcu
    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
    AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
LEFT JOIN information_schema.REFERENTIAL_CONSTRAINTS rc
    ON tc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
    AND tc.TABLE_SCHEMA = rc.CONSTRAINT_SCHEMA
WHERE tc.TABLE_SCHEMA = 'your_database'
AND tc.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'FOREIGN KEY', 'UNIQUE')
ORDER BY tc.TABLE_NAME, tc.CONSTRAINT_TYPE;

-- Find orphaned foreign keys (referencing non-existent tables)
SELECT
    kcu.TABLE_NAME,
    kcu.COLUMN_NAME,
    kcu.REFERENCED_TABLE_NAME,
    kcu.REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE kcu
LEFT JOIN information_schema.TABLES t
    ON kcu.REFERENCED_TABLE_NAME = t.TABLE_NAME
    AND kcu.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE kcu.TABLE_SCHEMA = 'your_database'
AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
AND t.TABLE_NAME IS NULL;

User and Privilege Information

-- List all users
SELECT
    User,
    Host,
    account_locked,
    password_expired
FROM mysql.user
ORDER BY User, Host;

-- Show user privileges
SELECT
    GRANTEE,
    PRIVILEGE_TYPE,
    IS_GRANTABLE
FROM information_schema.USER_PRIVILEGES
WHERE GRANTEE LIKE '%username%'
ORDER BY PRIVILEGE_TYPE;

-- Show database-level privileges
SELECT
    GRANTEE,
    TABLE_SCHEMA,
    PRIVILEGE_TYPE
FROM information_schema.SCHEMA_PRIVILEGES
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY GRANTEE, PRIVILEGE_TYPE;

-- Show table-level privileges
SELECT
    GRANTEE,
    TABLE_SCHEMA,
    TABLE_NAME,
    PRIVILEGE_TYPE
FROM information_schema.TABLE_PRIVILEGES
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_NAME, GRANTEE;

Stored Routine Information

-- List all stored procedures and functions
SELECT
    ROUTINE_NAME,
    ROUTINE_TYPE,
    DATA_TYPE,
    ROUTINE_DEFINITION,
    CREATED,
    LAST_ALTERED
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'your_database'
ORDER BY ROUTINE_TYPE, ROUTINE_NAME;

-- Find procedures with specific parameter
SELECT
    SPECIFIC_NAME AS ROUTINE_NAME,
    PARAMETER_NAME,
    DATA_TYPE,
    PARAMETER_MODE
FROM information_schema.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'your_database'
ORDER BY SPECIFIC_NAME, ORDINAL_POSITION;

-- List all triggers
SELECT
    TRIGGER_NAME,
    EVENT_MANIPULATION,
    EVENT_OBJECT_TABLE,
    ACTION_TIMING,
    ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'your_database'
ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING;

-- List all events
SELECT
    EVENT_NAME,
    EVENT_DEFINITION,
    INTERVAL_VALUE,
    INTERVAL_FIELD,
    STATUS,
    STARTS,
    ENDS,
    LAST_EXECUTED
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = 'your_database'
ORDER BY EVENT_NAME;

Generating Documentation

-- Generate comprehensive table documentation
SELECT
    t.TABLE_NAME,
    t.ENGINE,
    t.TABLE_ROWS,
    ROUND((t.DATA_LENGTH + t.INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size_MB',
    t.TABLE_COMMENT,
    GROUP_CONCAT(
        CONCAT(
            c.COLUMN_NAME, ' ',
            c.COLUMN_TYPE,
            IF(c.IS_NULLABLE = 'NO', ' NOT NULL', ''),
            IF(c.COLUMN_KEY = 'PRI', ' PRIMARY KEY', ''),
            IF(c.COLUMN_KEY = 'UNI', ' UNIQUE', ''),
            IF(c.EXTRA != '', CONCAT(' ', c.EXTRA), '')
        )
        ORDER BY c.ORDINAL_POSITION
        SEPARATOR '\n'
    ) AS COLUMNS
FROM information_schema.TABLES t
JOIN information_schema.COLUMNS c
    ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
    AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_SCHEMA = 'your_database'
AND t.TABLE_TYPE = 'BASE TABLE'
GROUP BY t.TABLE_NAME, t.ENGINE, t.TABLE_ROWS, t.DATA_LENGTH, t.INDEX_LENGTH, t.TABLE_COMMENT
ORDER BY t.TABLE_NAME;

Real-World Use Cases

-- 1. Database Health Check
CREATE VIEW db_health_check AS
SELECT
    'Tables without PK' AS issue,
    COUNT(*) AS count
FROM information_schema.TABLES t
WHERE TABLE_SCHEMA = 'your_database'
AND TABLE_TYPE = 'BASE TABLE'
AND NOT EXISTS (
    SELECT 1 FROM information_schema.TABLE_CONSTRAINTS tc
    WHERE tc.TABLE_SCHEMA = t.TABLE_SCHEMA
    AND tc.TABLE_NAME = t.TABLE_NAME
    AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
)
UNION ALL
SELECT
    'Tables without indexes',
    COUNT(DISTINCT t.TABLE_NAME)
FROM information_schema.TABLES t
LEFT JOIN information_schema.STATISTICS s
    ON t.TABLE_SCHEMA = s.TABLE_SCHEMA
    AND t.TABLE_NAME = s.TABLE_NAME
WHERE t.TABLE_SCHEMA = 'your_database'
AND t.TABLE_TYPE = 'BASE TABLE'
AND s.INDEX_NAME IS NULL
UNION ALL
SELECT
    'Large TEXT/BLOB columns',
    COUNT(*)
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
AND DATA_TYPE IN ('mediumtext', 'longtext', 'mediumblob', 'longblob');

-- 2. Generate CREATE TABLE statements
DELIMITER //
CREATE PROCEDURE generate_create_statement(IN tbl_name VARCHAR(64))
BEGIN
    SELECT CONCAT(
        'CREATE TABLE `', tbl_name, '` (\n',
        GROUP_CONCAT(
            CONCAT(
                '  `', COLUMN_NAME, '` ',
                COLUMN_TYPE,
                IF(IS_NULLABLE = 'NO', ' NOT NULL', ' NULL'),
                IF(COLUMN_DEFAULT IS NOT NULL, CONCAT(' DEFAULT \'', COLUMN_DEFAULT, '\''), ''),
                IF(EXTRA != '', CONCAT(' ', EXTRA), '')
            )
            ORDER BY ORDINAL_POSITION
            SEPARATOR ',\n'
        ),
        '\n);')
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = tbl_name;
END //
DELIMITER ;

-- 3. Find similar table structures
SELECT
    t1.TABLE_NAME AS table1,
    t2.TABLE_NAME AS table2,
    COUNT(*) AS matching_columns
FROM information_schema.COLUMNS t1
JOIN information_schema.COLUMNS t2
    ON t1.COLUMN_NAME = t2.COLUMN_NAME
    AND t1.DATA_TYPE = t2.DATA_TYPE
    AND t1.TABLE_NAME < t2.TABLE_NAME
WHERE t1.TABLE_SCHEMA = 'your_database'
AND t2.TABLE_SCHEMA = 'your_database'
GROUP BY t1.TABLE_NAME, t2.TABLE_NAME
HAVING COUNT(*) > 5
ORDER BY matching_columns DESC;

-- 4. Storage engine analysis
SELECT
    ENGINE,
    COUNT(*) AS table_count,
    ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Total Size (MB)'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
GROUP BY ENGINE
ORDER BY SUM(DATA_LENGTH + INDEX_LENGTH) DESC;

Performance Analysis

-- Find tables needing optimization
SELECT
    TABLE_NAME,
    ENGINE,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size (MB)',
    ROUND(DATA_FREE / 1024 / 1024, 2) AS 'Free Space (MB)',
    ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH)) * 100, 2) AS 'Fragmentation %'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
AND DATA_FREE > 0
ORDER BY DATA_FREE DESC;

-- Identify unused indexes (requires sys schema)
SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    s.INDEX_NAME,
    ROUND((s.STAT_VALUE * @@innodb_page_size) / 1024 / 1024, 2) AS 'Index Size (MB)'
FROM information_schema.TABLES t
JOIN information_schema.STATISTICS s
    ON t.TABLE_SCHEMA = s.TABLE_SCHEMA
    AND t.TABLE_NAME = s.TABLE_NAME
JOIN information_schema.INNODB_SYS_TABLESTATS ist
    ON CONCAT(t.TABLE_SCHEMA, '/', t.TABLE_NAME) = ist.NAME
WHERE t.TABLE_SCHEMA = 'your_database'
AND s.INDEX_NAME != 'PRIMARY'
GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME, s.INDEX_NAME;

Building Dynamic Tools

DELIMITER //

-- Procedure to backup all tables dynamically
CREATE PROCEDURE backup_all_tables()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(64);
    DECLARE backup_cursor CURSOR FOR
        SELECT TABLE_NAME
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_TYPE = 'BASE TABLE';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN backup_cursor;

    backup_loop: LOOP
        FETCH backup_cursor INTO tbl_name;

        IF done THEN
            LEAVE backup_loop;
        END IF;

        -- Create backup table
        SET @sql = CONCAT('CREATE TABLE ', tbl_name, '_backup AS SELECT * FROM ', tbl_name);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP;

    CLOSE backup_cursor;
END //

-- Generate ERD-like relationship map
CREATE PROCEDURE show_table_relationships()
BEGIN
    SELECT
        CONCAT(kcu.TABLE_NAME, '.', kcu.COLUMN_NAME) AS 'Foreign Key',
        CONCAT(kcu.REFERENCED_TABLE_NAME, '.', kcu.REFERENCED_COLUMN_NAME) AS 'References',
        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
        AND kcu.TABLE_SCHEMA = rc.CONSTRAINT_SCHEMA
    WHERE kcu.TABLE_SCHEMA = DATABASE()
    AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
    ORDER BY kcu.TABLE_NAME, kcu.COLUMN_NAME;
END //

DELIMITER ;

Practice Exercise:

Create a Database Documentation Generator:

Create a stored procedure that generates comprehensive documentation for a database including:
- List of all tables with row counts and sizes
- All columns with data types and constraints
- All indexes and their columns
- All foreign key relationships
- All stored procedures and functions
- All triggers and events
Store the documentation in a 'database_documentation' table
Include generation timestamp and database version
Test it on your current database

Important Information Schema Tables

SCHEMATA             - Database information
TABLES               - Table metadata
COLUMNS              - Column details
STATISTICS           - Index information
TABLE_CONSTRAINTS    - Constraint definitions
KEY_COLUMN_USAGE     - Foreign key relationships
REFERENTIAL_CONSTRAINTS - FK update/delete rules
ROUTINES             - Stored procedures/functions
PARAMETERS           - Routine parameters
TRIGGERS             - Trigger definitions
EVENTS               - Scheduled events
USER_PRIVILEGES      - User permissions
SCHEMA_PRIVILEGES    - Database permissions
TABLE_PRIVILEGES     - Table permissions
VIEWS                - View definitions
PARTITIONS           - Table partition info
ENGINES              - Available storage engines

Best Practices

✓ Use Information Schema for database introspection
✓ Generate documentation automatically
✓ Monitor database health regularly
✓ Identify optimization opportunities
✓ Build dynamic database tools
✓ Document schema changes
✓ Audit security and privileges
✓ Compare development vs production schemas
✗ Don't query Information Schema too frequently
✗ Avoid complex joins on Information Schema
✗ Don't rely on Information Schema for real-time data

Summary

In this lesson, you learned:

Information Schema provides metadata about your database
Query tables, columns, indexes, and constraints programmatically
Generate documentation automatically from schema metadata
Identify database health issues and optimization opportunities
Build dynamic database tools using schema information
Audit user privileges and security settings
Create portable queries using ANSI SQL standards
Information Schema is essential for database administration

Module Complete! You've mastered Advanced Database Objects including Views, Stored Procedures, Functions, Triggers, Events, Cursors, and Information Schema. These powerful tools enable sophisticated database automation and management!