Extracts DDL and catalog metadata from a live source system and writes local artifact files that downstream skills consume. Produces manifest.json, per-object DDL files in ddl/, and per-object catalog JSON files in catalog/. This is the first command in the pipeline and must complete before any discovery, scoping, or profiling work.

/setup-ddl

No arguments. The command runs an interactive workflow that prompts for database and schema selection before extracting anything.

Trigger phrases: "set up DDL", "extract DDL from SQL Server", "populate DDL", "connect to the remote database and get DDL", "pull DDL from the source database".

The extract subcommand runs the full extraction non-interactively — connect, query, assemble DDL, write catalog, enrich — in a single CLI call. It does not prompt for confirmation and does not write a .staging/ directory.

uv run --project <shared-path> setup-ddl extract \
  --database <database> \
  --schemas dbo,silver \
  [--project-root .]

manifest.json must already exist and contain a valid source technology before running extract. The extraction flow writes the active source endpoint to runtime.source and extraction metadata to extraction.*.

Enriched catalog fields (scoping, profile, refactor) written by earlier skill runs are preserved across re-extractions.

• oracledb Python package: uv pip install oracledb

• Three environment variables: ORACLE_USER, ORACLE_PASSWORD, ORACLE_DSN

• The Oracle user must have SELECT_CATALOG_ROLE granted (required for DBMS_METADATA.GET_DDL and ALL_* views):

  GRANT SELECT_CATALOG_ROLE TO <user>;
  GRANT SELECT ANY DICTIONARY TO <user>;

• toolbox binary on PATH -- the mssql MCP server requires genai-toolbox. Run toolbox --version to verify. Install from https://github.com/googleapis/genai-toolbox/releases if missing.

• Bootstrap environment variables set -- the mssql MCP server reads these before runtime.source exists in manifest.json:

  Variable	Description	Example
  MSSQL_HOST	SQL Server hostname or IP	localhost
  MSSQL_PORT	SQL Server port	1433
  MSSQL_DB	Default database (use master if no specific default)	master
  SA_PASSWORD	SQL login password	(from env)

• Project root -- the skill confirms pwd with the user before proceeding. If manifest.json already exists, the skill reads runtime.source and extraction.schemas from it and skips database selection.

Lists user databases (database_id > 4) via sys.databases and asks the user to pick one. Skipped when manifest.json already exists.

Lists non-system schemas with object counts (tables, procedures, views, functions) and presents an all option. The user picks one or more schemas.

Runs count queries and catalog signal availability checks, then presents a summary:

Extraction preview for [database]
Schemas: silver, gold

  Object counts:
    Tables:     42
    Procedures: 18
    Views:      5
    Functions:  3

  Catalog signals available:
    Primary keys:    38 constraints
    Foreign keys:    22 constraints
    Identity cols:   15 columns
    CDC-tracked:     2 tables

  DDL files will be written to:     ./ddl/
  Catalog files will be written to: ./catalog/

The user must confirm before any files are written.

uv run --project <shared-path> setup-ddl write-manifest \
  --technology sql_server \
  --database <database> \
  --schemas <comma-separated-schemas>

For each object type, queries OBJECT_DEFINITION() via the mssql MCP tool, saves the result to .staging/<type>.json, then runs:

uv run --project <shared-path> setup-ddl assemble-modules \
  --input ./.staging/<type>.json \
  --type <procedures|views|functions>

Queries sys.tables, sys.columns, sys.types, and sys.identity_columns, saves to .staging/table_columns.json, then runs:

uv run --project <shared-path> setup-ddl assemble-tables \
  --input ./.staging/table_columns.json

Runs 12 catalog signal queries plus 3 DMF reference queries via MCP, saving each result to .staging/. Then processes all staging files in one pass:

uv run --project <shared-path> setup-ddl write-catalog \
  --staging-dir ./.staging \
  --database <database>

uv run --project <shared-path> catalog-enrich --project-root .

Augments catalog files with AST-derived references that catalog queries miss: CTAS/SELECT INTO targets, TRUNCATE targets, and indirect writers through EXEC call chains. Entries carry "detection": "ast_scan".

Displays a summary of all extracted files and tells the user they can run discover or scoping next.

This skill reads from a live SQL Server via the mssql MCP tool. No local catalog files are consumed.

Technology-to-dialect mapping:

Initial fields written by setup-ddl (profile and scoping sections are added later by other skills):

See Skill Analyzing Object for the full procedure catalog schema.

The 12 catalog signal queries produce these staging files in .staging/:

{
  "schema_version": "1.0",
  "technology": "sql_server",
  "dialect": "tsql",
  "runtime": {
    "source": {
      "technology": "sql_server",
      "dialect": "tsql",
      "connection": {
        "host": "localhost",
        "port": "1433",
        "database": "AdventureWorksDW",
        "user": "sa",
        "driver": "FreeTDS"
      }
    }
  },
  "extraction": {
    "schemas": ["dbo", "silver", "gold"],
    "extracted_at": "2025-03-15T14:30:00Z"
  }
}

{
  "schema": "silver",
  "name": "DimCustomer",
  "columns": [
    { "name": "CustomerKey", "sql_type": "BIGINT", "is_nullable": false, "is_identity": true },
    { "name": "FirstName", "sql_type": "NVARCHAR(50)", "is_nullable": true, "is_identity": false },
    { "name": "Region", "sql_type": "NVARCHAR(50)", "is_nullable": true, "is_identity": false }
  ],
  "primary_keys": [
    { "constraint_name": "PK_DimCustomer", "columns": ["CustomerKey"] }
  ],
  "unique_indexes": [],
  "foreign_keys": [
    {
      "constraint_name": "FK_DimCustomer_Region",
      "columns": ["Region"],
      "referenced_schema": "silver",
      "referenced_table": "DimGeography",
      "referenced_columns": ["GeographyKey"]
    }
  ],
  "auto_increment_columns": [
    { "column": "CustomerKey", "seed": 1, "increment": 1, "mechanism": "identity" }
  ],
  "change_capture": { "enabled": true, "mechanism": "cdc" },
  "sensitivity_classifications": [
    { "column": "FirstName", "label": "Confidential", "information_type": "Name" }
  ],
  "referenced_by": {
    "procedures": {
      "in_scope": [
        { "schema": "silver", "name": "usp_load_DimCustomer", "is_selected": true, "is_updated": true, "detection": "catalog_query" }
      ],
      "out_of_scope": []
    },
    "views": { "in_scope": [], "out_of_scope": [] },
    "functions": { "in_scope": [], "out_of_scope": [] }
  }
}