Skip to content
METRIC OAK

New: the 3-week Data Platform Assessment: fixed fee, priced roadmap. See what's included →

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

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

  1. Inventory from SQL Server’s own catalog.
  2. Cut what never runs.
  3. Convert in dependency order, grouped by application module.
  4. 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:

  1. Pick realistic inputs (parameters, starting data) for each procedure.
  2. Run them through the SQL Server version and save the outputs.
  3. Run the same inputs through the converted version.
  4. 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.

Frequently asked questions

How do I find which stored procedures are actually used?

Snapshot sys.dm_exec_procedure_stats regularly over a full business cycle, including month and year end, because a row disappears when the procedure leaves the plan cache. Functions and triggers have their own views (sys.dm_exec_function_stats, sys.dm_exec_trigger_stats). Combine the results with sys.sql_expression_dependencies for the call graph. Procedures that never run and that nothing calls are candidates to drop instead of converting.

In what order should stored procedures be converted?

In dependency order, leaf functions and procedures first, grouped by the application module that calls them. Each group can then be tested end to end before moving on.

Can AI tools convert T-SQL stored procedures?

They convert syntax quickly, and that helps. They don't prove the converted code returns the same results. With thousands of procedures, the test harness that runs the same inputs through old and new code and diffs the outputs is most of the work.

What T-SQL features are hardest to convert to PostgreSQL?

Procedures that return result sets (Postgres procedures have no return value; use functions that return tables, or refcursors), TRY/CATCH (EXCEPTION blocks, which are more expensive to enter), temp-table chains, @@ROWCOUNT (GET DIAGNOSTICS) and OUTPUT clauses (RETURNING), dynamic SQL, and collation or case-sensitivity differences.

Have 30 minutes? Get a senior architect’s view of your data platform.

No sales deck. You talk to the engineer who would lead the work.

Book an architecture review
Hemant WalchaleYou talk to Hemant Walchale, founder145 published guidesProfiles: LinkedIn · Clutch · GoodFirms

Free, takes 20 seconds

Get your starter data platform roadmap

Pick your platform and your main goal. You get a 90-day plan written by senior data engineers: what to do first, next and after.

You’ll also get our monthly email (confirm it in your inbox). Unsubscribe any time. Privacy policy.