SQL Server
Converting thousands of SQL Server stored procedures: a method that works for any target
Tables and data are the easy part of a SQL Server exit. A method for the stored procedures: inventory from the catalog, cut dead code, convert in dependency order, and prove each one by diffing outputs. Works for Postgres, Snowflake or Databricks.

Hemant Walchale
· 5 min read
A developer asked about a SQL Server migration with hundreds of tables and “almost thousands” of stored procedures. The replies said: it’s hard, do it in phases, try a conversion tool. All true, but there was no method. The tables and data are the easy part. The procedures are where the months go, whether the target is Postgres, Snowflake or Databricks.
The short answer
- Inventory from SQL Server’s own catalog.
- Cut what never runs.
- Convert in dependency order, grouped by application module.
- Prove each one by running the same inputs through old and new and diffing the outputs.
1. Inventory from the catalog
You don’t need a tool to start. SQL Server already knows:
| Question | Where to look |
|---|---|
| What objects exist and their code | sys.sql_modules joined to sys.objects |
| What calls what | sys.sql_expression_dependencies |
| What actually runs, and how often | sys.dm_exec_procedure_stats, plus sys.dm_exec_function_stats and sys.dm_exec_trigger_stats |
| Which jobs call which procedures | the command column of msdb.dbo.sysjobsteps |
Run this in each database. The stats view covers every database on the instance and object IDs are only unique within one, so filter on database_id. It can also hold more than one row per procedure (one per cached plan), so sum them:
WITH ps AS (
SELECT object_id,
SUM(execution_count) AS execution_count,
MAX(last_execution_time) AS last_execution_time
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID()
GROUP BY object_id
)
SELECT SCHEMA_NAME(o.schema_id) AS schema_name, o.name, o.type_desc,
LEN(m.definition) AS chars,
ps.execution_count, ps.last_execution_time
FROM sys.objects o
JOIN sys.sql_modules m ON m.object_id = o.object_id
LEFT JOIN ps ON ps.object_id = o.object_id
ORDER BY ps.execution_count DESC;
A row in dm_exec_procedure_stats lives only as long as the procedure’s plan stays cached, so a restart or cache pressure wipes it. Save a snapshot on a schedule over a full business cycle, including month end and year end, before trusting it. A procedure with no row may simply have been evicted.
Two gaps in the dependency view: Microsoft notes it doesn’t track temporary tables or temporary procedures, and because it reads names written in the code, calls built inside dynamic SQL strings won’t appear. Search sys.sql_modules for EXEC( and sp_executesql to find those.
2. Cut dead code
Large estates often carry procedures that never run: old reports, one-off fixes, copies with _v2 and _old suffixes.
Anything that hasn’t run over a full cycle and isn’t called by anything else goes on a “drop” list, signed off by the application owner. Every procedure you drop is one you don’t convert, test or maintain.
3. Convert in dependency order
Use the call graph to order the work:
- Leaves first: functions and procedures that call nothing else.
- Group by module: procedures called by the same screen, job or report move together, so each phase can be tested end to end.
- Big ones last: the 2,000-line procedures with dynamic SQL and cursors deserve their own design. Often they become set-based SQL or pipeline steps rather than a line-by-line translation.
4. Prove each one by diffing outputs
Syntax conversion is the quick part, whether a person, a tool or an LLM does it. The proof is the harness:
- Pick realistic inputs (parameters, starting data) for each procedure.
- Run them through the SQL Server version and save the outputs.
- Run the same inputs through the converted version.
- Diff row counts, key-measure sums, and rows by hash.
Build this harness before converting at scale. With thousands of procedures it’s most of the work, and it’s what turns “it compiles” into “it’s right”. The same idea applies to the data itself (see data migration reconciliation).
T-SQL features that don’t map cleanly
To PostgreSQL:
| T-SQL | PostgreSQL | Watch out for |
|---|---|---|
| Procedure returning rows | Function with RETURNS TABLE or RETURN QUERY, or a refcursor |
Procedures have no return value, only output parameters, and are called with CALL |
TRY/CATCH |
BEGIN ... EXCEPTION WHEN ... END |
The PL/pgSQL docs warn that a block with EXCEPTION is significantly more expensive to enter and exit |
#temp tables |
CREATE TEMP TABLE |
Dropped at the end of the session (or transaction), not when the procedure ends |
@@ROWCOUNT |
GET DIAGNOSTICS n = ROW_COUNT |
Read it straight after the statement |
OUTPUT clause |
RETURNING |
Different syntax and scoping |
| Trigger | A trigger function plus CREATE TRIGGER |
Two objects per trigger |
| Case-insensitive collation | Deterministic (case-sensitive) collations by default | String comparisons and sorting can change results |
To Snowflake or Databricks, row-by-row logic (cursors, WHILE loops) should usually become set-based SQL or pipeline steps. Converting it literally works but runs slowly. See SQL Server to Snowflake and SQL Server to Databricks.
A compatibility layer as a first step
On AWS, Babelfish for Aurora PostgreSQL accepts connections from SQL Server clients over the TDS protocol and runs T-SQL “with some differences”, per AWS. It lets you move first and convert gradually. Before relying on it, run the open-source Babelfish Compass tool on your generated DDL, as AWS recommends, and check the T-SQL limitations for your version. It’s not a full answer: you still test everything and eventually rewrite what it doesn’t support.
For the cost side of a Postgres move, see SQL Server to PostgreSQL: what the estimates miss.
Not sure where your own platform stands? Take the free 3-minute data platform scorecard. Need people to do this alongside your team? See how our consulting engagements work. Unfamiliar terms are explained in the data engineering glossary.