Shell ScriptingDocumentationDatabase CentricAdvancedMay 2026

Shell Scripting — Database Centric: Database Schema Documentation

Auto-generate always-current schema docs from information_schema — Markdown table docs, column inventories, foreign key relationship maps, Mermaid ERD generation, and index audits.

Database schema documentation is perpetually out of date the moment you write it manually. Shell scripts that extract schema information directly from information_schema generate always-current documentation, ERD inputs, and data dictionaries that stay in sync with the actual database.

1 Auto-generate schema documentation

BASH

#!/usr/bin/env bash
# document_schema.sh — Generate Markdown schema docs from MySQL

DB_CONF="/etc/myapp/mysql.conf"
DB_NAME="myapp"
OUTPUT="schema-$(date +%Y%m%d).md"

db() { mysql --defaults-file="${DB_CONF}" -BNs -D information_schema -e "$1"; }

{
  echo "# Database Schema: ${DB_NAME}"
  echo "Generated: $(date '+%Y-%m-%d %H:%M')"
  echo ""

  # Table of contents
  echo "## Tables"
  db "SELECT CONCAT('- [',TABLE_NAME,'](#',LOWER(TABLE_NAME),')')
      FROM TABLES WHERE TABLE_SCHEMA='${DB_NAME}' ORDER BY TABLE_NAME"
  echo ""

  # Per-table documentation
  db "SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA='${DB_NAME}'
      AND TABLE_TYPE='BASE TABLE' ORDER BY TABLE_NAME" |   while read -r tbl; do
    ROW_COUNT=$(db "SELECT TABLE_ROWS FROM TABLES
                    WHERE TABLE_SCHEMA='${DB_NAME}' AND TABLE_NAME='${tbl}'")
    COMMENT=$(db "SELECT TABLE_COMMENT FROM TABLES
                  WHERE TABLE_SCHEMA='${DB_NAME}' AND TABLE_NAME='${tbl}'")

    echo "## ${tbl}"
    [[ -n "${COMMENT}" && "${COMMENT}" != "NULL" ]] && echo "> ${COMMENT}"
    echo ""
    echo "Approximate rows: ${ROW_COUNT}"
    echo ""

    echo "| Column | Type | Nullable | Default | Key | Comment |"
    echo "|--------|------|----------|---------|-----|---------|"
    db "SELECT COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE,COALESCE(COLUMN_DEFAULT,''),
               COLUMN_KEY,COALESCE(COLUMN_COMMENT,'')
        FROM COLUMNS
        WHERE TABLE_SCHEMA='${DB_NAME}' AND TABLE_NAME='${tbl}'
        ORDER BY ORDINAL_POSITION" |       awk -F$'\t' '{printf "| %-20s | %-25s | %-8s | %-10s | %-3s | %s |\n",$1,$2,$3,$4,$5,$6}'

    echo ""
  done
} > "${OUTPUT}"

echo "Schema documented: ${OUTPUT}"

2 Extract foreign keys and generate ERD input

BASH

# ── Extract all foreign key relationships ─────────────────
mysql --defaults-file="${DB_CONF}" -BNs information_schema -e "
  SELECT
    kcu.TABLE_NAME,
    kcu.COLUMN_NAME,
    kcu.REFERENCED_TABLE_NAME,
    kcu.REFERENCED_COLUMN_NAME,
    rc.DELETE_RULE,
    rc.UPDATE_RULE
  FROM KEY_COLUMN_USAGE kcu
  JOIN REFERENTIAL_CONSTRAINTS rc
    ON rc.CONSTRAINT_SCHEMA=kcu.CONSTRAINT_SCHEMA
    AND rc.CONSTRAINT_NAME=kcu.CONSTRAINT_NAME
  WHERE kcu.TABLE_SCHEMA='${DB_NAME}'
  AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
  ORDER BY kcu.TABLE_NAME" |   awk -F$'\t' '{printf "%s.%s -> %s.%s (DELETE:%s UPDATE:%s)\n",$1,$2,$3,$4,$5,$6}'

# ── Generate Mermaid ERD diagram ──────────────────────────
{
  echo "erDiagram"
  mysql --defaults-file="${DB_CONF}" -BNs information_schema -e "
    SELECT kcu.TABLE_NAME, kcu.REFERENCED_TABLE_NAME
    FROM KEY_COLUMN_USAGE kcu
    WHERE kcu.TABLE_SCHEMA='${DB_NAME}'
    AND kcu.REFERENCED_TABLE_NAME IS NOT NULL" |     awk -F$'\t' '{printf "  %s }|--|| %s : has\n",$2,$1}'
} > schema.mermaid

echo "Mermaid ERD generated: schema.mermaid"

# ── Index inventory ───────────────────────────────────────
mysql --defaults-file="${DB_CONF}" -BNs information_schema -e "
  SELECT TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS cols,
    NON_UNIQUE, INDEX_TYPE
  FROM STATISTICS
  WHERE TABLE_SCHEMA='${DB_NAME}'
  GROUP BY TABLE_NAME, INDEX_NAME
  ORDER BY TABLE_NAME, INDEX_NAME" |   awk -F$'\t' '{printf "  %-20s %-30s (%s) unique=%s type=%s\n",$1,$2,$3,(1-$4),$5}

✔ ✔ Documentati — Generate schema docs as part of every migration run so they are always current. Store in version control alongside migration files. Use TABLE_COMMENT and COLUMN_COMMENT fields in MySQL to embed documentation directly in the schema — they appear in the generated docs automatically. Export to Markdown for Git, CSV for spreadsheets, and Mermaid for embedded diagrams in wikis.