Metadata

9 min read•Learning SQL

Metadata

Metadata is data about data. In databases, metadata describes the structure of your database itself—tables, columns, indexes, constraints, and more. You can query this metadata to understand schemas, build dynamic SQL, or create administrative tools.

Information Schema

The information_schema is a standard set of views available in most databases. It provides a portable way to query metadata.

Listing Tables

SQL

-- All tables in current database
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public'  -- PostgreSQL
   OR table_schema = DATABASE();  -- MySQL
 
-- Filter by type
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema = 'bookstore';

table_name	table_type
authors	BASE TABLE
books	BASE TABLE
customers	BASE TABLE
orders	BASE TABLE
order_items	BASE TABLE

Listing Columns

SQL

SELECT
    column_name,
    data_type,
    is_nullable,
    column_default,
    character_maximum_length
FROM information_schema.columns
WHERE table_name = 'books'
ORDER BY ordinal_position;

column_name	data_type	is_nullable	column_default	character_maximum_length
book_id	int	NO	NULL	NULL
title	varchar	NO	NULL	200
author_id	int	NO	NULL	NULL
price	decimal	NO	NULL	NULL
stock_quantity	int	YES	0	NULL

Listing Constraints

SQL

-- Primary and unique keys
SELECT
    constraint_name,
    constraint_type,
    table_name
FROM information_schema.table_constraints
WHERE table_schema = 'bookstore';

constraint_name	constraint_type	table_name
PRIMARY	PRIMARY KEY	authors
PRIMARY	PRIMARY KEY	books
uk_isbn	UNIQUE	books
fk_author	FOREIGN KEY	books

Foreign Key Details

SQL

SELECT
    tc.constraint_name,
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table,
    ccu.column_name AS foreign_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
    ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';

Note

The information_schema is read-only. You cannot modify database structure through it—only query it.

Database-Specific System Catalogs

While information_schema is standard, each database has its own system catalogs with more detailed information.

MySQL System Tables

SQL

-- Tables
SELECT * FROM mysql.innodb_table_stats;
 
-- Columns (alternative)
SHOW COLUMNS FROM books;
DESCRIBE books;
 
-- Indexes
SHOW INDEX FROM books;
 
-- Create statement
SHOW CREATE TABLE books;

PostgreSQL System Catalogs

SQL

-- Tables (pg_class)
SELECT relname, relkind, reltuples
FROM pg_class
WHERE relkind = 'r'  -- 'r' = regular table
  AND relnamespace = 'public'::regnamespace;
 
-- Columns (pg_attribute)
SELECT attname, typname, attnotnull
FROM pg_attribute a
JOIN pg_type t ON a.atttypid = t.oid
WHERE attrelid = 'books'::regclass
  AND attnum > 0;
 
-- Indexes (pg_indexes)
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'books';
 
-- Functions
SELECT proname, pronargs
FROM pg_proc
WHERE pronamespace = 'public'::regnamespace;

SQL Server System Views

SQL

-- Tables
SELECT name, type_desc
FROM sys.tables;
 
-- Columns
SELECT c.name, t.name AS type, c.is_nullable
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE object_id = OBJECT_ID('books');
 
-- Indexes
SELECT name, type_desc, is_unique
FROM sys.indexes
WHERE object_id = OBJECT_ID('books');

Querying Database Statistics

Row Counts

SQL

-- Approximate row count (fast)
-- PostgreSQL
SELECT relname, reltuples::bigint AS row_count
FROM pg_class
WHERE relkind = 'r'
ORDER BY reltuples DESC;
 
-- MySQL
SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema = 'bookstore';
 
-- Exact count (slow for large tables)
SELECT
    (SELECT COUNT(*) FROM authors) AS authors,
    (SELECT COUNT(*) FROM books) AS books,
    (SELECT COUNT(*) FROM orders) AS orders;

Table Sizes

SQL

-- PostgreSQL
SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS data_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
 
-- MySQL
SELECT
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'bookstore'
ORDER BY data_length + index_length DESC;

Index Usage

SQL

-- PostgreSQL: Most used indexes
SELECT
    indexrelname AS index_name,
    relname AS table_name,
    idx_scan AS index_scans,
    idx_tup_read AS rows_read
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
 
-- Unused indexes (candidates for removal)
SELECT indexrelname, relname
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%pkey';

Dynamic SQL with Metadata

Use metadata to generate SQL dynamically.

Generate Column List

SQL

-- Build SELECT with all columns
SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position)
FROM information_schema.columns
WHERE table_name = 'books';
-- Result: book_id,title,author_id,isbn,price,stock_quantity,publication_year,created_at

Generate Audit Triggers

SQL

-- Generate INSERT triggers for all tables
SELECT CONCAT(
    'CREATE TRIGGER audit_', table_name, '_insert ',
    'AFTER INSERT ON ', table_name, ' ',
    'FOR EACH ROW ',
    'INSERT INTO audit_log (table_name, action, row_id, changed_at) ',
    'VALUES (''', table_name, ''', ''INSERT'', NEW.',
    (SELECT column_name
     FROM information_schema.columns
     WHERE table_name = t.table_name
       AND ordinal_position = 1),
    ', NOW());'
) AS trigger_statement
FROM information_schema.tables t
WHERE table_schema = 'bookstore'
  AND table_type = 'BASE TABLE';

Schema Comparison

SQL

-- Find columns in prod but not in dev
SELECT column_name, table_name
FROM prod_db.information_schema.columns
WHERE table_schema = 'public'
EXCEPT
SELECT column_name, table_name
FROM dev_db.information_schema.columns
WHERE table_schema = 'public';

Building a Documentation Generator:

SQL

SELECT
    t.table_name,
    t.table_comment AS description,
    GROUP_CONCAT(
        CONCAT(
            c.column_name, ' (', c.data_type,
            IF(c.is_nullable = 'NO', ', required', ''),
            ')'
        )
        ORDER BY c.ordinal_position
        SEPARATOR ', '
    ) AS columns
FROM information_schema.tables t
JOIN information_schema.columns c ON t.table_name = c.table_name
WHERE t.table_schema = 'bookstore'
GROUP BY t.table_name, t.table_comment;

Checking Object Existence

Before creating or modifying objects, check if they exist:

SQL

-- Check table exists
SELECT EXISTS (
    SELECT 1 FROM information_schema.tables
    WHERE table_name = 'books'
) AS table_exists;
 
-- Check column exists
SELECT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'books' AND column_name = 'isbn'
) AS column_exists;
 
-- Check index exists
SELECT EXISTS (
    SELECT 1 FROM information_schema.statistics
    WHERE table_name = 'books' AND index_name = 'idx_author'
) AS index_exists;

Conditional DDL

SQL

-- MySQL: Add column only if it doesn't exist
SET @exists = (
    SELECT COUNT(*) FROM information_schema.columns
    WHERE table_name = 'books' AND column_name = 'subtitle'
);
 
SET @sql = IF(@exists = 0,
    'ALTER TABLE books ADD COLUMN subtitle VARCHAR(200)',
    'SELECT "Column already exists"'
);
 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Data Dictionary Queries

Find Tables with Specific Columns

SQL

-- Which tables have a 'created_at' column?
SELECT DISTINCT table_name
FROM information_schema.columns
WHERE column_name = 'created_at'
  AND table_schema = 'bookstore';
 
-- Tables with email columns (potential PII)
SELECT table_name, column_name
FROM information_schema.columns
WHERE column_name LIKE '%email%'
   OR column_name LIKE '%mail%';

Find Orphaned Foreign Keys

SQL

-- Foreign keys pointing to non-existent tables
SELECT
    tc.table_name,
    tc.constraint_name,
    kcu.column_name,
    kcu.referenced_table_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND NOT EXISTS (
      SELECT 1 FROM information_schema.tables
      WHERE table_name = kcu.referenced_table_name
  );

Column Data Type Analysis

SQL

-- Find all VARCHAR columns and their max lengths
SELECT
    table_name,
    column_name,
    character_maximum_length
FROM information_schema.columns
WHERE data_type = 'varchar'
  AND table_schema = 'bookstore'
ORDER BY character_maximum_length DESC;
 
-- Find columns that might be too small
SELECT
    table_name,
    column_name,
    character_maximum_length AS max_length
FROM information_schema.columns
WHERE data_type = 'varchar'
  AND character_maximum_length < 50
  AND column_name LIKE '%name%';

Practical Examples

Example 1: Schema Documentation Generator

SQL

-- Generate markdown documentation
SELECT CONCAT(
    '## ', t.table_name, '\n\n',
    COALESCE(t.table_comment, 'No description'), '\n\n',
    '| Column | Type | Nullable | Default |\n',
    '|--------|------|----------|--------|\n',
    GROUP_CONCAT(
        CONCAT(
            '| ', c.column_name,
            ' | ', c.data_type,
            COALESCE(CONCAT('(', c.character_maximum_length, ')'), ''),
            ' | ', c.is_nullable,
            ' | ', COALESCE(c.column_default, 'NULL'),
            ' |'
        )
        ORDER BY c.ordinal_position
        SEPARATOR '\n'
    ),
    '\n'
) AS markdown
FROM information_schema.tables t
JOIN information_schema.columns c
    ON t.table_name = c.table_name
   AND t.table_schema = c.table_schema
WHERE t.table_schema = 'bookstore'
  AND t.table_type = 'BASE TABLE'
GROUP BY t.table_name, t.table_comment;

Example 2: Database Health Check

SQL

-- Comprehensive database health report
SELECT 'Tables' AS category, COUNT(*) AS count
FROM information_schema.tables
WHERE table_schema = 'bookstore' AND table_type = 'BASE TABLE'
 
UNION ALL
 
SELECT 'Views', COUNT(*)
FROM information_schema.views
WHERE table_schema = 'bookstore'
 
UNION ALL
 
SELECT 'Columns', COUNT(*)
FROM information_schema.columns
WHERE table_schema = 'bookstore'
 
UNION ALL
 
SELECT 'Indexes', COUNT(DISTINCT index_name)
FROM information_schema.statistics
WHERE table_schema = 'bookstore'
 
UNION ALL
 
SELECT 'Foreign Keys', COUNT(*)
FROM information_schema.table_constraints
WHERE constraint_type = 'FOREIGN KEY'
  AND table_schema = 'bookstore'
 
UNION ALL
 
SELECT 'Tables without PK', COUNT(*)
FROM information_schema.tables t
WHERE t.table_schema = 'bookstore'
  AND t.table_type = 'BASE TABLE'
  AND NOT EXISTS (
      SELECT 1 FROM information_schema.table_constraints tc
      WHERE tc.table_name = t.table_name
        AND tc.constraint_type = 'PRIMARY KEY'
  );

Example 3: Migration Script Generator

SQL

-- Generate CREATE TABLE statements from existing schema
SELECT CONCAT(
    'CREATE TABLE ', table_name, ' (\n',
    GROUP_CONCAT(
        CONCAT(
            '    ', column_name, ' ',
            UPPER(data_type),
            CASE
                WHEN character_maximum_length IS NOT NULL
                THEN CONCAT('(', character_maximum_length, ')')
                WHEN numeric_precision IS NOT NULL
                THEN CONCAT('(', numeric_precision,
                           COALESCE(CONCAT(',', numeric_scale), ''), ')')
                ELSE ''
            END,
            IF(is_nullable = 'NO', ' NOT NULL', ''),
            IF(column_default IS NOT NULL,
               CONCAT(' DEFAULT ', column_default), '')
        )
        ORDER BY ordinal_position
        SEPARATOR ',\n'
    ),
    '\n);'
) AS create_statement
FROM information_schema.columns
WHERE table_schema = 'bookstore'
GROUP BY table_name;

Example 4: Find Missing Indexes

SQL

-- Tables with foreign keys but no index
SELECT
    tc.table_name,
    kcu.column_name AS fk_column,
    'Missing index on foreign key' AS issue
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
LEFT JOIN information_schema.statistics s
    ON kcu.table_name = s.table_name
   AND kcu.column_name = s.column_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND s.index_name IS NULL;

Summary

This chapter covered:

Information Schema: Standard metadata views across databases
System Catalogs: Database-specific detailed metadata
Statistics: Row counts, table sizes, index usage
Dynamic SQL: Generating queries from metadata
Health Checks: Validating schema integrity

Congratulations! You've completed Learning SQL. You now have a solid foundation in:

Database fundamentals and SQL syntax
Querying, filtering, and joining data
Aggregation and subqueries
Data manipulation and conditional logic
Transactions and data integrity
Performance optimization with indexes
Views for abstraction and security
Metadata for database introspection

The best way to solidify this knowledge is practice. Set up a database, create tables, and start writing queries!