Data Dictionary Generator

🎯 How to Use This Tool

This tool generates comprehensive data dictionaries and relationship diagrams from your database schema. Follow these steps:

Step 1: Export Your Database Schema

For MySQL/MariaDB:

SELECT 
    TABLE_NAME as tableName,
    COLUMN_NAME as columnName,
    DATA_TYPE as dataType,
    CHARACTER_MAXIMUM_LENGTH as maxLength,
    IS_NULLABLE as nullable,
    COLUMN_KEY as columnKey,
    COLUMN_DEFAULT as defaultValue,
    COLUMN_COMMENT as description
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

For PostgreSQL:

SELECT 
    c.table_name as "tableName",
    c.column_name as "columnName",
    c.data_type as "dataType",
    c.character_maximum_length as "maxLength",
    c.is_nullable as "nullable",
    CASE WHEN pk.column_name IS NOT NULL THEN 'PRI' ELSE '' END as "columnKey",
    c.column_default as "defaultValue",
    pgd.description as "description"
FROM information_schema.columns c
LEFT JOIN (
    SELECT ku.table_name, ku.column_name
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage ku
        ON tc.constraint_name = ku.constraint_name
    WHERE tc.constraint_type = 'PRIMARY KEY'
) pk ON c.table_name = pk.table_name AND c.column_name = pk.column_name
LEFT JOIN pg_catalog.pg_statio_all_tables st ON c.table_name = st.relname
LEFT JOIN pg_catalog.pg_description pgd ON pgd.objoid = st.relid
WHERE c.table_schema = 'public'
ORDER BY c.table_name, c.ordinal_position;

For SQL Server:

SELECT 
    t.TABLE_NAME as tableName,
    c.COLUMN_NAME as columnName,
    c.DATA_TYPE as dataType,
    c.CHARACTER_MAXIMUM_LENGTH as maxLength,
    c.IS_NULLABLE as nullable,
    CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 'PRI' ELSE '' END as columnKey,
    c.COLUMN_DEFAULT as defaultValue,
    ep.value as description
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME
LEFT JOIN (
    SELECT ku.TABLE_NAME, ku.COLUMN_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
        ON tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
    WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
) pk ON c.TABLE_NAME = pk.TABLE_NAME AND c.COLUMN_NAME = pk.COLUMN_NAME
LEFT JOIN sys.extended_properties ep ON ep.major_id = OBJECT_ID(c.TABLE_NAME)
WHERE t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_NAME, c.ORDINAL_POSITION;

Step 2: Get Foreign Key Relationships

For MySQL/MariaDB:

SELECT 
    TABLE_NAME as tableName,
    COLUMN_NAME as columnName,
    REFERENCED_TABLE_NAME as referencedTable,
    REFERENCED_COLUMN_NAME as referencedColumn
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_database_name'
    AND REFERENCED_TABLE_NAME IS NOT NULL;

For PostgreSQL:

SELECT
    tc.table_name as "tableName",
    kcu.column_name as "columnName",
    ccu.table_name as "referencedTable",
    ccu.column_name as "referencedColumn"
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 ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
    AND tc.table_schema = 'public';

Step 3: Format as JSON

Convert your query results to JSON format. You can use online tools or this structure:

{
  "database": "Your Database Name",
  "tables": [
    {
      "name": "users",
      "description": "User accounts table",
      "columns": [
        {
          "name": "id",
          "type": "int",
          "nullable": false,
          "primaryKey": true,
          "description": "Primary key"
        }
      ]
    }
  ],
  "relationships": [
    {
      "fromTable": "orders",
      "fromColumn": "user_id",
      "toTable": "users",
      "toColumn": "id"
    }
  ]
}

Step 4: Try the Example

Go to the Generator tab and click "Load Example" to see a working sample.

📊 Data Dictionary Generator

🎯 How to Use This Tool

Step 1: Export Your Database Schema

Step 2: Get Foreign Key Relationships

Step 3: Format as JSON

Step 4: Try the Example