How to Read a Stored Procedure You Did Not Write

To read stored procedure code you did not write, start with its contract and side effects. Understanding what can change is more urgent than understanding every formatting choice.

Identify the Procedure and Its Contract

Confirm the database and schema before opening the definition. Procedures with similar names can serve different applications. Record the caller, the intended result, and the permissions used for execution.

SELECT SCHEMA_NAME(schema_id) AS schema_name,
       name, create_date, modify_date
FROM sys.procedures
WHERE is_ms_shipped = 0
ORDER BY schema_name, name;

Choose the procedure you need and replace the example name in the following queries. These queries inspect metadata without executing that procedure. Reading unfamiliar code should not begin by running it against production.

DECLARE @Procedure sysname = N'dbo.YourProcedure';
SELECT p.parameter_id, p.name,
       TYPE_NAME(p.user_type_id) AS data_type,
       p.max_length, p.precision, p.scale, p.is_output
FROM sys.parameters AS p
WHERE p.object_id = OBJECT_ID(@Procedure)
ORDER BY p.parameter_id;
SELECT OBJECT_DEFINITION(OBJECT_ID(@Procedure)) AS procedure_definition;

Read parameter defaults from the T-SQL definition as well as the catalog. Note output parameters, return codes, and result sets separately. They are different ways of communicating with the caller.

Mark Every Write Before Reading Every Join

Scan for INSERT, UPDATE, DELETE, MERGE, and commands that change object definitions. Also follow EXEC calls because another procedure may perform the actual write. A procedure named GetSomething is not proof of read-only behavior.

For each write, identify the target, filter, and expected scope. Ask what happens when a parameter is NULL or missing. An optional filter can turn a single-customer action into a much broader operation.

Temporary tables and table variables are useful landmarks. Record what each one represents and how its rows are produced. Do not get stuck interpreting a long expression before understanding why that intermediate set exists.

Trace Transaction Ownership

Find BEGIN TRANSACTION, COMMIT, ROLLBACK, and any savepoint handling. Then ask whether the caller might already own a transaction. A nested BEGIN does not create an independently committable transaction.

Look for SET XACT_ABORT and TRY…CATCH together with transaction-state checks. @@TRANCOUNT describes transaction nesting, while XACT_STATE indicates whether an active transaction can commit. Neither alone explains the whole ownership contract.

SELECT @@TRANCOUNT AS transaction_count,
       XACT_STATE() AS transaction_state;
DBCC USEROPTIONS;

This reports the current session, not every possible future caller. Use it to understand your test context. When reviewing error paths, trace what happens to both transaction state and the error returned to the application.

Follow Dependencies With Known Blind Spots

Catalog dependencies provide a useful starting map of named references. They can show tables, views, functions, and other objects mentioned in persisted expressions. They are not a complete record of everything runtime code might touch.

DECLARE @Procedure sysname = N'dbo.YourProcedure';
SELECT referenced_server_name, referenced_database_name,
       referenced_schema_name, referenced_entity_name,
       referenced_id, is_caller_dependent, is_ambiguous
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID(@Procedure);

Dynamic SQL can assemble names that never appear as a normal dependency. Synonyms, cross-database access, and caller-dependent resolution need extra attention. Read how identifiers and values enter the generated statement.

A NULL definition can mean insufficient metadata permission or an encrypted module. Do not call the procedure empty on that evidence. Ask for the approved source or appropriate visibility before making a change.

Separate Business Rules From Mechanics

Describe each major branch in a short sentence. For example, an inactive account is rejected before an invoice is created. That explanation is easier to verify with an owner than a page of SQL.

Mark rules whose purpose is unclear, especially date boundaries and special status values. A strange condition may preserve a business exception. Removing it because it looks untidy is not a maintenance strategy.

Also check the assumed row count behind variable assignments and joins. Duplicate input can change an update or multiply a result set. The absence of a uniqueness constraint is a question worth recording.

Write a Small Test Plan Before Editing

Choose representative inputs, empty results, boundary dates, and an expected failure. Run those cases in an approved test database with known data. Compare returned values and changed rows, not only whether the procedure completed.

Save a short reading note covering inputs, outputs, writes, dependencies, and transaction ownership. Include unresolved assumptions rather than disguising them as facts. The next person should not have to rediscover every important turn in the code.

Reading unfamiliar SQL is not a line-count challenge, it is a search for the contract.

This post was rewritten from scratch in September 2026. The original, published on 2011-11-04, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.