Extracts DDL and catalog metadata from a live SQL Server (or Fabric Warehouse) 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 skill in the pipeline and must complete before any discovery, scoping, or profiling work.

/setup-ddl

No arguments. The skill 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".

• 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.

• Environment variables set -- the mssql MCP server reads these at startup:

  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 source_database and extracted_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",
  "source_database": "AdventureWorksDW",
  "extracted_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": [] }
  }
}