Database Documentation Generator

Overview

Generate comprehensive database documentation by introspecting live PostgreSQL or MySQL schemas, extracting table structures, column descriptions, relationships, indexes, constraints, stored procedures, and views. Produces human-readable documentation in Markdown format including entity-relationship descriptions, data dictionary, and column-level metadata.

Prerequisites

• Database credentials with read access to information_schema, pg_catalog (PostgreSQL), or system tables (MySQL)
• psql or mysql CLI for executing introspection queries
• Target output directory for generated documentation files
• Existing column comments (COMMENT ON COLUMN) enhance output quality significantly
• Knowledge of the business domain for meaningful table/column descriptions

Instructions

1. Extract the complete table inventory: SELECT table_name, obj_description((table_schema || '.' || table_name)::regclass) AS table_comment FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE' ORDER BY table_name (PostgreSQL). For MySQL: SELECT TABLE_NAME, TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE().

2. For each table, extract column details: SELECT c.column_name, c.data_type, c.character_maximum_length, c.is_nullable, c.column_default, pgd.description AS column_comment FROM information_schema.columns c LEFT JOIN pg_catalog.pg_description pgd ON pgd.objsubid = c.ordinal_position AND pgd.objoid = (c.table_schema || '.' || c.table_name)::regclass WHERE c.table_name = 'target_table' ORDER BY c.ordinal_position.