T-SQL migration, run by an agent fleet.

What the Documenter reads in a T-SQL estate

The T-SQL layer is where the work hides. Tables and columns describe shape; the stored procedures, views, functions and ad-hoc batches describe behaviour, and in most Microsoft data estates that procedural layer holds the bulk of the business logic. The Documenter (the agent that builds the knowledge base before any rebuild begins) reads that logic instead of cataloguing names. The assessment gate exists to surface what the code actually does: the branch that only fires at month-end, the cursor that should have been a set-based join, the dynamic string nobody has read in years. Autonomous in the loop, accountable at the gates.

Microsoft positions Microsoft Fabric as the path forward for SQL warehouse workloads, so observations are recorded against a documented destination (the Fabric Data Warehouse, or on the Databricks path Unity Catalog objects and Delta tables) and the rebuild lands there as dbt models or in your framework.

Stored procedures: control flow, not just statements

A stored procedure is a procedural batch, and the Documenter reads it as one. It records the parameters and their directions, the local variables, and the control-of-flow that wires them together: IF/ELSE branching, WHILE loops, BEGIN...END blocks, and the TRY...CATCH error handling. It captures transaction boundaries, because a multi-step write inside a single transaction has different rebuild semantics from the same steps committed independently. It records temp tables and table variables as intermediate state, and follows nested procedure calls so the real call graph is reconstructed rather than assumed from the entry point. Long imperative procedures (cursors, loops, branching, temp tables, multi-step transactions) are the costliest to convert to a set-based or declarative target, so they are flagged: on the dbt path they must be decomposed into ordered, idempotent models, or handled as dbt Python models or macros, because dbt’s unit of work is a SELECT and not a side-effecting batch.

Views and functions: nesting and target support

Views are named SELECTs, but they are rarely flat. The Documenter follows views over views, because a view that looks trivial may sit on a stack of nested views that together embed non-trivial logic, and that logic has to be reproduced as a whole. Functions are read by category, because their fate differs sharply by target: scalar UDFs, inline table-valued functions, and multi-statement table-valued functions are recorded separately. Scalar UDFs and multi-statement TVFs are re-expressed rather than ported (as SQL UDFs, Python UDFs, or set-based logic) because semantics and performance differ on the target, while an inline TVF often re-parameterises cleanly.

Dynamic SQL: tracing what is not there

Dynamic SQL is the highest-risk construct in the T-SQL layer for the same reason it is in SSIS: the statement that executes is assembled at runtime and is not present statically in the object. The Documenter reads how the string is built (the concatenations, the variables that feed them, the IF branches that swap fragments in and out) and traces it to the values that drive it, so the effective statement run by EXEC() or sp_executesql is captured rather than guessed. This is the most common place for undocumented behaviour, so every instance is surfaced before the build-test-run loop begins.

Dialect quirks and cross-object references

The Documenter flags the constructs that do not survive a platform move unchanged. Cross-database and linked-server references, three- and four-part names like db.schema.object and linkedserver.db.schema.object: are inventoried as portability hotspots, because availability is constrained on cloud SQL targets and each is a dependency that may not exist on the destination. It records the dialect-specific constructs that drive rewrite decisions: cursors, MERGE, table variables and temp tables, IDENTITY, sequences, computed columns, DML and DDL triggers, OUTPUT clauses, windowing functions, and CROSS APPLY / OUTER APPLY. Where the source already runs on a Synapse dedicated SQL pool, the reduced dialect is itself a signal of what has been adapted away from full SQL Server: no @@NESTLEVEL, an 8-level nesting cap, no INSERT...EXECUTE, constrained scalar UDFs, and blob-type limits that force dynamic strings to be chunked.

How constructs land on the target

On the Databricks path the Documenter records which procedural features each object depends on, because Databricks has closed much of the historical gap. CREATE PROCEDURE (procedures in Unity Catalog, invoked by CALL), SQL scripting (IF/CASE, WHILE/REPEAT/LOOP/FOR, condition handlers in BEGIN...END), and cursor processing all exist, but several are gated behind recent Databricks Runtime versions, so the gating is treated as version-dependent and confirmed before it is relied on. Some constructs still need genuine redesign: T-SQL MERGE becomes Delta Lake MERGE INTO rather than a direct port (and OUTPUT-style MERGE has no equivalent), triggers become explicit pipeline or job steps, scalar UDFs are re-expressed, and linked servers give way to the Unity Catalog three-level namespace and federation. The build-test-run loop then iterates until green so the rebuilt logic matches the source.