sql-ddl-regenerator 0.1.0

Universal SQL DDL regenerator starting with production-grade SQL Server table DDL extraction.

SQL DDL Regenerator v0.1.0

A production-oriented SQL DDL regenerator for live database metadata.

Version 0.1.0 supports SQL Server table DDL generation through a clean adapter structure. The tool connects to a SQL Server database, reads system catalog metadata for one table, and emits deterministic T-SQL that can recreate the table structure and related table-level artifacts.

What this version generates

For SQL Server base tables, v0.1.0 generates:

• CREATE TABLE statement with schema-qualified, safely quoted identifiers.
• Column definitions for built-in types, alias/user-defined types, XML schema collections, computed columns, sparse columns, column sets, FILESTREAM, ROWGUIDCOL, hidden columns, temporal generated columns, identity columns, collations, defaults, dynamic data masking, and Always Encrypted metadata.
• Primary key and unique constraints with backing index options.
• Check constraints, including disabled and untrusted state restoration.
• Foreign keys, including column order, referential actions, disabled state, untrusted state, and NOT FOR REPLICATION.
• Non-constraint indexes, including included columns, descending keys, filtered indexes, columnstore indexes, XML indexes, hash indexes, partition scheme target, filegroup target, selected index options, and compression metadata.
• System-versioned temporal table period and SYSTEM_VERSIONING settings.
• Memory-optimized table options exposed by catalog metadata.
• Graph node/edge table marker when exposed by the SQL Server catalog.
• FileTable creation statement.
• Full-text index statement when present.
• Table DML triggers with disabled state restoration.
• Table and column extended properties.
• Optional USE [database], optional guarded DROP TABLE, and optional GO batch separators.

Design principles

• Fail closed for unsupported lossless regeneration cases.
• Use SQL Server catalog views instead of INFORMATION_SCHEMA because SQL Server-specific features are not fully represented in INFORMATION_SCHEMA.
• Never concatenate user input into catalog queries. Schema and table names are passed as parameters.
• Quote every emitted SQL identifier with SQL Server bracket quoting.
• Keep passwords out of logs.
• Support CLI and Python API usage.

Requirements

• Python 3.10+
• Microsoft ODBC Driver for SQL Server installed on the machine running the tool.
• A SQL Server login with metadata visibility for the target table and related constraints/indexes/triggers/properties.
• Network access to the SQL Server instance.

Install the Microsoft ODBC Driver separately because it is a system-level dependency. The Python package depends on pyodbc.

Install from source

cd sql-ddl-regenerator-v0.1.0
python -m venv .venv
source .venv/bin/activate
python -m pip install --upgrade pip
python -m pip install -e .

On Windows PowerShell:

cd sql-ddl-regenerator-v0.1.0
py -m venv .venv
.\.venv\Scripts\Activate.ps1
python -m pip install --upgrade pip
python -m pip install -e .

Verify installation:

sql-ddl-regenerator --version
sqlddl --version

Both console commands point to the same CLI.

Quick start

Generate DDL for dbo.Customer and print to stdout:

sql-ddl-regenerator generate \
  --dbms sqlserver \
  --server localhost,1433 \
  --database SalesDb \
  --schema dbo \
  --table Customer \
  --username sa \
  --prompt-password \
  --trust-server-certificate yes

Write the result to a file:

sql-ddl-regenerator generate \
  --server localhost,1433 \
  --database SalesDb \
  --schema dbo \
  --table Customer \
  --username sa \
  --prompt-password \
  --output ddl/dbo.Customer.sql \
  --include-use-database

Use a raw ODBC connection string:

sql-ddl-regenerator generate \
  --connection-string 'DRIVER={ODBC Driver 18 for SQL Server};SERVER=localhost,1433;DATABASE=SalesDb;UID=sa;PWD=StrongPassword;Encrypt=yes;TrustServerCertificate=yes;' \
  --schema dbo \
  --table Customer

Use Windows integrated authentication where supported:

sql-ddl-regenerator generate \
  --server localhost \
  --database SalesDb \
  --trusted-connection \
  --schema dbo \
  --table Customer

Use Microsoft Entra / Azure AD authentication through the ODBC driver:

sql-ddl-regenerator generate \
  --server your-server.database.windows.net \
  --database SalesDb \
  --authentication ActiveDirectoryInteractive \
  --username [e-mail] \
  --schema dbo \
  --table Customer

Environment variables

Every connection option can be supplied through environment variables. CLI flags take precedence.

export SQLDDL_SERVER='localhost,1433'
export SQLDDL_DATABASE='SalesDb'
export SQLDDL_USERNAME='sa'
export SQLDDL_PASSWORD='StrongPassword'
export SQLDDL_DRIVER='ODBC Driver 18 for SQL Server'
export SQLDDL_ENCRYPT='yes'
export SQLDDL_TRUST_SERVER_CERTIFICATE='yes'

sql-ddl-regenerator generate --schema dbo --table Customer --output ddl/customer.sql

Supported environment variables:

Variable	Purpose
SQLDDL_CONNECTION_STRING	Raw ODBC connection string. Overrides individual connection fields.
SQLDDL_SERVER	SQL Server host, host\instance, or host,port.
SQLDDL_PORT	SQL Server port when not embedded in SQLDDL_SERVER.
SQLDDL_DATABASE	Database name.
SQLDDL_USERNAME	SQL login or Entra username.
SQLDDL_PASSWORD	Password.
SQLDDL_DRIVER	ODBC driver name. Defaults to ODBC Driver 18 for SQL Server.
SQLDDL_TRUSTED_CONNECTION	true, false, yes, no, 1, or 0.
SQLDDL_AUTHENTICATION	ODBC authentication mode, for example ActiveDirectoryInteractive.
SQLDDL_ENCRYPT	ODBC encryption setting. Defaults to yes.
SQLDDL_TRUST_SERVER_CERTIFICATE	yes or no. Defaults to no.
SQLDDL_APPLICATION_INTENT	ReadOnly or ReadWrite.
SQLDDL_CONNECT_TIMEOUT	Connection timeout in seconds.
SQLDDL_QUERY_TIMEOUT	Query timeout in seconds.

CLI reference

sql-ddl-regenerator generate --help

Important output flags:

Flag	Behavior
--output PATH	Writes DDL to file. Without it, writes to stdout.
--include-use-database	Adds USE [database];.
--include-drop-if-exists	Adds guarded DROP TABLE.
--no-indexes	Excludes non-constraint indexes. Primary key and unique constraints are still included.
--no-foreign-keys	Excludes foreign keys.
--no-triggers	Excludes triggers.
--no-extended-properties	Excludes extended properties.
--no-fulltext	Excludes full-text index DDL.
--no-go	Removes GO batch separators.
--verbose	Enables debug logs.
--json-logs	Emits logs as JSON.

Python API

Use an existing pyodbc connection:

import pyodbc
from sql_ddl_regenerator import generate_sqlserver_table_ddl
from sql_ddl_regenerator.dbms.sqlserver import RenderOptions

connection = pyodbc.connect(
    "DRIVER={ODBC Driver 18 for SQL Server};"
    "SERVER=localhost,1433;"
    "DATABASE=SalesDb;"
    "UID=sa;"
    "PWD=StrongPassword;"
    "Encrypt=yes;"
    "TrustServerCertificate=yes;"
)

ddl = generate_sqlserver_table_ddl(
    connection=connection,
    schema="dbo",
    table="Customer",
    render_options=RenderOptions(include_use_database=True),
)
print(ddl)

Use a connection config:

from sql_ddl_regenerator import generate_sqlserver_table_ddl
from sql_ddl_regenerator.dbms.sqlserver import RenderOptions, SqlServerConnectionConfig

config = SqlServerConnectionConfig(
    server="localhost,1433",
    database="SalesDb",
    username="sa",
    password="StrongPassword",
    trust_server_certificate="yes",
)

ddl = generate_sqlserver_table_ddl(
    config=config,
    schema="dbo",
    table="Customer",
    render_options=RenderOptions(include_drop_if_exists=True),
)

Project structure

sql-ddl-regenerator-v0.1.0/
├── README.md
├── pyproject.toml
├── examples/
│   ├── example_output.sql
│   └── sqlserver.env.example
├── sql_ddl_regenerator/
│   ├── __init__.py
│   ├── __main__.py
│   ├── api.py
│   ├── cli.py
│   ├── core/
│   │   ├── errors.py
│   │   ├── identifiers.py
│   │   └── logging.py
│   └── dbms/
│       ├── base.py
│       └── sqlserver/
│           ├── connection.py
│           ├── generator.py
│           ├── introspector.py
│           ├── models.py
│           ├── queries.py
│           └── renderer.py
└── tests/
    ├── test_connection.py
    ├── test_identifiers.py
    └── test_renderer.py

How it works

1. The CLI or Python API receives connection details, schema, and table name.
2. The SQL Server adapter opens a read-only metadata session through ODBC.
3. The introspector reads version-safe SQL Server catalog metadata. It checks catalog feature flags before referencing newer catalog columns.
4. The metadata is normalized into typed dataclasses.
5. The renderer emits deterministic T-SQL from the normalized metadata.
6. Unsupported lossless cases raise a typed exception instead of silently producing incomplete DDL.

SQL Server coverage notes

This version scripts SQL Server user tables from sys.tables. It is designed for table DDL regeneration, not full database scripting.

Generated DDL may depend on objects outside the requested table. Examples:

• Referenced tables for foreign keys.
• User-defined data types.
• XML schema collections.
• Column encryption keys and column master keys.
• Full-text catalogs, stoplists, and search property lists.
• Partition schemes and functions.
• Filegroups and memory-optimized filegroups.
• Trigger dependencies.

Those objects are referenced by name because they are separate database-level or schema-level artifacts.

Fail-closed behavior

The generator raises a clear error when it detects a feature that cannot be regenerated safely as part of a single-table script. In v0.1.0 this includes SQL Server ledger tables because ledger regeneration can require database-level artifacts and digest/view behavior that is not table-local. It also fails closed for spatial and JSON indexes instead of emitting incomplete index DDL.

Security

• Use --prompt-password or SQLDDL_PASSWORD instead of putting passwords directly into shell history.
• Prefer Encrypt=yes with TrustServerCertificate=no for production.
• Use least-privilege SQL users that can view metadata for the target objects.
• The tool only reads catalog metadata and sets read-uncommitted isolation for metadata reads.
• Connection strings are redacted in debug logs.

Testing

Run unit tests:

python -m pip install -e '.[dev]'
python -m pytest

Run the CLI module directly during development:

python -m sql_ddl_regenerator --version
python -m sql_ddl_regenerator generate --help

Packaging

Build a wheel and source distribution:

python -m pip install build
python -m build

Install the built wheel:

python -m pip install dist/sql_ddl_regenerator-0.1.0-py3-none-any.whl

Exit codes

Code	Meaning
0	Success.
1	Expected application error: configuration, connection, object not found, or unsupported feature.
2	CLI argument parsing error.
130	Interrupted by user.