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
informationschema, pgcatalog (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
- Extract the complete table inventory:
SELECT tablename, objdescription((tableschema || '.' || tablename)::regclass) AS tablecomment FROM informationschema.tables WHERE tableschema = 'public' AND tabletype = 'BASE TABLE' ORDER BY tablename (PostgreSQL). For MySQL: SELECT TABLENAME, TABLECOMMENT FROM informationschema.TABLES WHERE TABLE_SCHEMA = DATABASE().
- For each table, extract column details:
SELECT c.columnname, c.datatype, c.charactermaximumlength, c.isnullable, c.columndefault, pgd.description AS columncomment FROM informationschema.columns c LEFT JOIN pgcatalog.pgdescription pgd ON pgd.objsubid = c.ordinalposition AND pgd.objoid = (c.tableschema || '.' || c.tablename)::regclass WHERE c.tablename = 'targettable' ORDER BY c.ordinalposition.
- Extract primary key and unique constraint definitions:
SELECT tc.constraintname, tc.constrainttype, kcu.columnname FROM informationschema.tableconstraints tc JOIN informationschema.keycolumnusage kcu ON tc.constraintname = kcu.constraintname WHERE tc.tablename = 'targettable' AND tc.constraint_type IN ('PRIMARY KEY', 'UNIQUE').
- Extract foreign key relationships to build the relationship map:
SELECT tc.tablename AS childtable, kcu.columnname AS childcolumn, ccu.tablename AS parenttable, ccu.columnname AS parentcolumn, rc.deleterule, rc.updaterule FROM informationschema.tableconstraints tc JOIN informationschema.keycolumnusage kcu ON tc.constraintname = kcu.constraintname JOIN informationschema.referentialconstraints rc ON tc.constraintname = rc.constraintname JOIN informationschema.constraintcolumnusage ccu ON rc.uniqueconstraintnam
