How to Extract Tables and Columns from SQL DDL Scripts

To extract tables, column lists, data types, default values, and primary key constraints from raw SQL DDL scripts without a live database connection, parse the structural CREATE TABLE text statements, isolate column attributes, and generate structured data dictionaries using the free in-browser SQL Schema Extractor tool.

Database administrators, data engineers, and software architects frequently receive Data Definition Language (DDL) scripts containing complete relational schemas. However, inspecting hundreds of CREATE TABLE statements across thousands of lines of SQL to construct a data dictionary or verify column constraints is slow and error-prone when executed manually. Running an enterprise database engine just to inspect schema definitions adds unnecessary infrastructure overhead, administrative privileges, and security risks.

Extracting database schema metadata directly from plain-text SQL files bypasses database server installation entirely. By leveraging deterministic lexical analysis and regular expressions, client-side extraction tools parse raw .sql files into flat two-dimensional spreadsheets (CSV) or structured JSON trees. This technical guide explains SQL DDL grammar, dialect syntax variations across PostgreSQL, MySQL, MariaDB, SQLite, and MSSQL, state-machine regex extraction algorithms, and step-by-step procedures for automated schema documentation and ETL data modeling.

Key Definitions: SQL DDL, CREATE TABLE, Data Types, Constraints, and Data Dictionaries

Extracting schema metadata from SQL files requires a clear understanding of core relational database concepts and DDL syntax structures:

• SQL DDL (Data Definition Language): A subset of SQL commands used to create, modify, and delete database structures (schemas, tables, indexes, views, and constraints). Unlike Data Manipulation Language (DML), which handles row inserts (INSERT INTO) and updates, DDL defines the structural blueprints of database objects.
• CREATE TABLE Statement: The primary DDL command that declares a new relational table, specifying its table name, schema scope, column names, column data types, default expressions, nullability rules, and structural constraints.
• Column Data Type: The attribute classification assigned to a column (e.g., VARCHAR(255), INTEGER, DECIMAL(10,2), TIMESTAMP, BOOLEAN, JSONB) that determines the range of permissible values, storage formatting, and arithmetic or string operations supported by the database engine.
• Primary Key (PK): A single column or composite set of columns that uniquely identifies each row within a table. Primary keys implicitly enforce a NOT NULL constraint and automatically generate a unique index.
• Foreign Key (FK): A relational constraint referencing the primary or unique key of another table, establishing referential integrity rules (such as ON DELETE CASCADE or ON UPDATE RESTRICT) between parent and child tables.
• Data Dictionary: A centralized metadata repository detailing database tables, column names, data types, precision, nullability flags, default values, key relationships, and descriptive comments required for database documentation, data governance, and data warehousing.

SQL DDL Syntax Variants Across Dialects: PostgreSQL, MySQL, MariaDB, SQLite, and MSSQL

While relational database systems adhere to the ISO/IEC 9075 SQL standard, major database vendors introduce distinct DDL syntax extensions, identifier quoting rules, auto-increment keywords, and metadata comment definitions. Parsers must handle these dialect variations seamlessly.

1. Identifier Quoting Conventions

Database dialects use different delimiters to quote table and column identifiers containing spaces, reserved keywords, or special characters:

• ANSI SQL & PostgreSQL: Enclose identifiers in standard double quotes: "users", "user_id".
• MySQL & MariaDB: Enclose identifiers in backticks: `users`, `user_id`.
• Microsoft SQL Server (MSSQL / T-SQL): Enclose identifiers in square brackets: [users], [user_id], or double quotes when QUOTED_IDENTIFIER is active.
• SQLite: Accepts double quotes, backticks, or square brackets interchangeably.

2. Auto-Incrementing Identifiers and Sequence Syntax

Generating auto-incrementing surrogate primary keys relies on dialect-specific keywords:

• PostgreSQL: Uses pseudo-types SERIAL / BIGSERIAL or the modern SQL standard GENERATED ALWAYS AS IDENTITY syntax.
• MySQL & MariaDB: Uses the AUTO_INCREMENT column modifier.
• MSSQL: Uses the IDENTITY(seed, increment) property (e.g., IDENTITY(1,1)).
• SQLite: Requires INTEGER PRIMARY KEY AUTOINCREMENT on integer columns.

3. Inline vs. Out-of-Line Constraint Definitions

Primary keys and foreign keys can be declared inline alongside individual column definitions, or out-of-line as separate table constraints at the bottom of the CREATE TABLE block:

-- Inline Constraint Syntax (MySQL / PostgreSQL / SQLite)
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)
);

-- Out-of-Line Composite Constraint Syntax (ANSI SQL Standard)
CREATE TABLE order_items (
    order_id INT NOT NULL,
    item_id INT NOT NULL,
    quantity INT DEFAULT 1,
    CONSTRAINT pk_order_items PRIMARY KEY (order_id, item_id),
    CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
);

4. Table and Column Metadata Comments

Documenting table business definitions varies significantly between database management systems:

• MySQL & MariaDB: Supports inline COMMENT 'text' directives directly inside the CREATE TABLE block.
• PostgreSQL: Executes separate post-creation DDL statements: COMMENT ON TABLE users IS 'Master user accounts'; and COMMENT ON COLUMN users.email IS 'Primary contact email';.
• MSSQL: Uses stored procedures to add extended metadata properties: EXEC sp_addextendedproperty @name=N'MS_Description', ....

How to Parse Table Definitions, Data Types, and Constraints Using Regex Algorithms

Extracting schema metadata from unparsed SQL DDL scripts requires structured text tokenization. Naive string splitting (such as splitting by commas or line breaks) fails because SQL statements contain embedded commas within parameters (such as DECIMAL(10, 2) or ENUM('active', 'pending')) and nested parenthesis blocks.

Formal Schema Representation

Formally, a relational database schema \( S \) is defined as a collection of table definitions:

\[ S = \{ T_1, T_2, \dots, T_k \} \]

Where each table \( T_i \) is represented as a 4-tuple:

\[ T_i = (N_i, C_i, P_i, F_i) \]

• \( N_i \): The qualified table name identifier.
• \( C_i = \{ c_{i,1}, c_{i,2}, \dots, c_{i,m} \} \): The ordered set of column definitions.
• \( P_i \subseteq C_i \): The set of attributes comprising the primary key.
• \( F_i \): The set of foreign key relational pairs referencing external tables \( (c_j \rightarrow T_{ref}.c_{ref}) \).

Each column \( c_j \) is further defined by its attribute parameters:

\[ c_j = (n_j, t_j, l_j, null_j, def_j) \]

Where \( n_j \) is the column name, \( t_j \) is the base data type, \( l_j \) is the length or precision parameter, \( null_j \in \{\text{TRUE}, \text{FALSE}\} \) represents nullability, and \( def_j \) represents the default expression.

Multi-Stage Lexical Parsing Pipeline

To accurately convert DDL text into structured tuple records, an automated client-side parser executes four sequential processing phases:

1. Comment & Directive Stripping: Removes single-line comments (-- comment and # comment) and multi-line block comments (/* comment */), as well as vendor-specific session directives (such as SET FOREIGN_KEY_CHECKS = 0;).
2. Table Block Extraction: Scans for CREATE TABLE block boundaries using balanced brace/parenthesis lexers or regular expressions:

   /CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?(?:"([^"]+)"|`([^`]+)`|\[([^\]]+)\]|([a-zA-Z0-9_\.]+))\s*\(([\s\S]*?)\)(?:;|\s*ENGINE|\s*DEFAULT)/gi

3. Parenthesis-Aware Column Tokenization: Iterates through the inner body of the CREATE TABLE block, tracking parenthesis depth to split top-level comma-delimited definitions while protecting inner parameters like NUMERIC(12, 4).
4. Attribute Parameter Classification: Tests each token against column pattern rules vs. table constraint rules (PRIMARY KEY (...), CONSTRAINT ... FOREIGN KEY (...)).

JavaScript Schema Parsing Implementation

The following client-side JavaScript implementation demonstrates how to parse SQL DDL scripts into a structured array of table objects containing column lists, data types, nullability flags, and primary keys:

function parseSqlDdl(sqlText) {
  // Step 1: Strip single-line and multi-line comments
  const cleanSql = sqlText
    .replace(/--.*$/gm, '')
    .replace(/#.*$/gm, '')
    .replace(/\/\*[\s\S]*?\*\//g, '');

  const tables = [];
  // Step 2: Match CREATE TABLE blocks across dialects
  const tableRegex = /CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?(?:"([^"]+)"|`([^`]+)`|\[([^\]]+)\]|([a-zA-Z0-9_\.]+))\s*\(([\s\S]*?)\)(?:\s*;|\s*ENGINE|\s*DEFAULT|\s*$)/gi;

  let match;
  while ((match = tableRegex.exec(cleanSql)) !== null) {
    const tableName = match[1] || match[2] || match[3] || match[4];
    const bodyText = match[5];
    
    const columns = [];
    const primaryKeys = new Set();
    const tokens = splitTableBodyTokens(bodyText);

    tokens.forEach(token => {
      const cleanToken = token.trim();
      if (!cleanToken) return;

      // Match Out-of-Line PRIMARY KEY constraint
      if (/^(?:CONSTRAINT\s+[`"\[]?\w+[`"\]]?\s+)?PRIMARY\s+KEY/i.test(cleanToken)) {
        const pkMatch = cleanToken.match(/PRIMARY\s+KEY\s*\(([^)]+)\)/i);
        if (pkMatch) {
          pkMatch[1].split(',').forEach(col => {
            primaryKeys.add(col.replace(/[`"\[\]\s]/g, ''));
          });
        }
        return;
      }

      // Skip generic table constraints (FOREIGN KEY, UNIQUE INDEX, CHECK)
      if (/^(?:CONSTRAINT|FOREIGN\s+KEY|UNIQUE|KEY|INDEX|CHECK)/i.test(cleanToken)) {
        return;
      }

      // Parse individual column definitions
      const colRegex = /^(?:"([^"]+)"|`([^`]+)`|\[([^\]]+)\]|([a-zA-Z0-9_]+))\s+([a-zA-Z0-9_\(\),\s]+?)(?:\s+(.*))?$/i;
      const colMatch = cleanToken.match(colRegex);

      if (colMatch) {
        const colName = colMatch[1] || colMatch[2] || colMatch[3] || colMatch[4];
        const dataTypeRaw = colMatch[5].trim();
        const constraintsRaw = colMatch[6] || '';

        const isNullable = !/NOT\s+NULL/i.test(constraintsRaw);
        const isPkInline = /PRIMARY\s+KEY/i.test(constraintsRaw);
        if (isPkInline) primaryKeys.add(colName);

        const defaultMatch = constraintsRaw.match(/DEFAULT\s+('([^']*)'|"[^"]*"|([^\s,]+))/i);
        const defaultValue = defaultMatch ? (defaultMatch[2] || defaultMatch[1]) : null;

        columns.push({
          name: colName,
          dataType: dataTypeRaw,
          nullable: isNullable,
          defaultValue: defaultValue,
          isPrimaryKey: false // Updated after scanning table constraints
        });
      }
    });

    // Mark primary key flags
    columns.forEach(col => {
      if (primaryKeys.has(col.name)) {
        col.isPrimaryKey = true;
      }
    });

    tables.push({ tableName, columns });
  }

  return tables;
}

// Parenthesis-aware token splitter
function splitTableBodyTokens(bodyText) {
  const tokens = [];
  let currentToken = '';
  let depth = 0;
  let inString = false;
  let quoteChar = '';

  for (let i = 0; i < bodyText.length; i++) {
    const char = bodyText[i];
    
    if ((char === "'" || char === '"' || char === '`') && bodyText[i - 1] !== '\\') {
      if (!inString) {
        inString = true;
        quoteChar = char;
      } else if (quoteChar === char) {
        inString = false;
      }
    }

    if (!inString) {
      if (char === '(') depth++;
      else if (char === ')') depth--;
      else if (char === ',' && depth === 0) {
        tokens.push(currentToken);
        currentToken = '';
        continue;
      }
    }
    currentToken += char;
  }
  if (currentToken.trim()) tokens.push(currentToken);
  return tokens;
}

Generating Automated Database Data Dictionaries for Schema Documentation & ETL Data Modeling

Automating SQL schema extraction into structured data dictionaries delivers significant productivity, governance, and architecture benefits across data engineering workflows:

1. Data Governance and Metadata Catalogs

Enterprise data teams must catalog all database tables, column names, data types, and primary key relationships across production databases. Converting legacy SQL DDL scripts into tabular CSV data dictionaries allows compliance teams to tag Personally Identifiable Information (PII) columns (e.g., email, phone_number, social_security_number) and track schema evolution across software releases.

2. ETL & Data Warehouse Target Modeling

Modern data stack platforms like Snowflake, Google BigQuery, Amazon Redshift, and Databricks require explicit target schemas when ingesting transactional data from OLTP databases (PostgreSQL or MySQL). Automated schema extraction produces formatted CSV/JSON field maps that allow data engineers to generate dbt staging model YAML files, Databricks Delta Lake DDL scripts, or Airflow pipeline schemas instantly.

3. Automated Code & API Generation

Extracted JSON schemas feed directly into software development toolchains to generate data access layers automatically:

• TypeScript & ORM Interfaces: Maps database column data types to TypeScript interface fields or Prisma/Drizzle ORM schema definitions.
• OpenAPI / Swagger Schemas: Transforms table column definitions into REST API request/response JSON Schema properties.
• GraphQL Type Definitions: Converts database table structures directly into GraphQL type objects and query fields.

How to Extract SQL Tables and Columns Privately in Your Browser

Follow this 5-step operational workflow to extract table names, column lists, data types, and primary key constraints from any SQL DDL script using EasyExtract's secure client-side extractor:

1. Supply Your SQL DDL Script or Dump File:
   Copy your raw SQL DDL code or drag and drop your .sql text file directly into the drop zone of the SQL Schema Extractor.
2. Execute Client-Side Lexical Tokenization:
   The WebAssembly engine parses the SQL text entirely inside your browser's sandboxed memory context. Zero data is transmitted to external servers, protecting proprietary database architecture and sensitive schema layouts.
3. Process Multi-Dialect Syntax and Constraints:
   The parser automatically detects your SQL dialect (PostgreSQL, MySQL, MariaDB, SQLite, or MSSQL), strips SQL comments, unwinds nested parenthesis definitions, and isolates table blocks.
4. Inspect the Generated Interactive Data Dictionary:
   Review the structured grid displaying table names, column lists, data types, character limits, precision parameters, nullability flags, default expressions, and primary key markers.
5. Export Relational Data Dictionary to CSV or JSON:
   Click export to download your processed schema as a flat CSV spreadsheet (for Microsoft Excel and Google Sheets) or a formatted JSON relational data model.

Exporting Database Schemas into Relational CSV & JSON Tables

EasyExtract's schema extraction engine converts complex database DDL structures into two standardized output formats suited for documentation and technical integration.

1. Flat Data Dictionary CSV Output (IETF RFC 4180 Compliant)

The CSV export linearizes database metadata into a flat two-dimensional table, creating one row per column definition across all extracted tables. Header columns follow standard data dictionary specifications:

"table_name","column_name","data_type","character_maximum_length","numeric_precision","is_nullable","column_default","is_primary_key"
"users","user_id","BIGINT",NULL,64,"NO",NULL,"YES"
"users","email","VARCHAR",255,NULL,"NO",NULL,"NO"
"users","status","VARCHAR",50,NULL,"YES","'active'","NO"
"users","created_at","TIMESTAMP",NULL,NULL,"NO","CURRENT_TIMESTAMP","NO"
"orders","order_id","BIGINT",NULL,64,"NO",NULL,"YES"
"orders","user_id","BIGINT",NULL,64,"NO",NULL,"NO"
"orders","total_amount","DECIMAL",NULL,10,"NO","0.00","NO"

### 2. Hierarchical JSON Schema Output

The JSON output presents the database schema as a structured tree object, preserving table grouping and nested column arrays for programatic integration into scripts or data pipelines:

{
  "database_schema": {
    "total_tables": 2,
    "tables": [
      {
        "table_name": "users",
        "columns": [
          {
            "name": "user_id",
            "data_type": "BIGINT",
            "nullable": false,
            "defaultValue": null,
            "isPrimaryKey": true
          },
          {
            "name": "email",
            "data_type": "VARCHAR(255)",
            "nullable": false,
            "defaultValue": null,
            "isPrimaryKey": false
          }
        ]
      },
      {
        "table_name": "orders",
        "columns": [
          {
            "name": "order_id",
            "data_type": "BIGINT",
            "nullable": false,
            "defaultValue": null,
            "isPrimaryKey": true
          }
        ]
      }
    ]
  }
}

Frequently Asked Questions

Related Reading & Tools

• SQL Schema Extractor Tool - Extract tables, columns, data types, and primary keys from SQL DDL online.
• SQL Data Extractor Tool - Extract INSERT INTO data records from SQL dump files directly into CSV spreadsheets.
• JSON Table Extractor Tool - Convert JSON arrays and nested object structures into flat CSV tables.
• CSV Table Extractor Tool - Parse, filter, and extract specific columns from large CSV files in browser.
• How to Extract Data from SQL File Without Database - Guide on extracting SQL DML insert statements and row values without database engines.
• Structured vs. Unstructured Data Guide - Comprehensive comparison of relational database schemas vs. unstructured text files.

Sources & References

• ISO/IEC 9075-1:2023: Information technology — Database languages — SQL — Part 1: Framework (SQL/Framework). International Organization for Standardization.
• PostgreSQL 16 Reference Manual: Chapter 5: Data Definition (DDL) & Table Basics. PostgreSQL Global Development Group.
• MySQL 8.0 Reference Manual: Chapter 13.1.20: CREATE TABLE Statement Syntax and Identifiers. Oracle Corporation.
• Microsoft SQL Server T-SQL Documentation: CREATE TABLE (Transact-SQL) Specifications and Identity Property Rules. Microsoft Corporation.
• SQLite Documentation: CREATE TABLE Command Syntax, Quoting Rules, and Auto-Increment Columns. SQLite Development Team.