Whose Change Is Your Script About to Overwrite?

A script that updates a procedure does not say "change this line". It says "this procedure is now the following". That difference can mean silently erasing a fix someone else made in production two weeks ago that nobody knows about. This article is about why that last look before execution matters.

The Silent Side of a Script

Updates to procedures, views, functions and triggers write the full body. The script contains the object's new state from beginning to end, and running it replaces the old state. This is not a flaw in the tooling, it is how it works.

The problem starts here: time passes between the moment the script is written and the moment it runs. A request is opened, goes to approval, waits, is scheduled into a maintenance window. In most organisations that interval is days, sometimes weeks. In the meantime someone else may have touched the same object.

The most common story is not malicious and usually goes like this: during an outage an emergency fix is applied to a procedure. The fix works, the outage closes. Nobody carries that fix back to the development environment. Three weeks later the planned update to the same procedure runs, and the emergency fix disappears as if it never existed. The same failure returns a few days later, and this time nobody can find the cause.

This is a consequence of production structure drifting away from the records. We covered the sources of that drift and how it is detected afterwards in a separate article: database schema drift. The subject here is not detection but the moment before execution.

Three Questions Before You Run

There are three questions to answer immediately before taking a change to production, and all three are answered from the same place: the current definition on the server.

1. What does this object look like on the server right now? Not how it looked the day the script was written, but how it looks now.
2. What is the difference between what the script will produce and the current state? If there is more difference than you expect, the excess is someone else's work.
3. When was this object last changed, and under which request? If the answer is "never" and there is still a difference, that change came from outside the pipeline.

The answer to the third question is especially valuable because it is a control test in its own right. The number of changes arriving from outside the pipeline is the most direct indicator of whether change management actually works. We listed all the routes into production in a separate article.

What the Comparison Shows

A comparison made before execution yields one of four outcomes, and each means something different.

Outcome	What to do
The difference is what you expected	Run it
No difference; the object is already in the target state	Stop and ask. Either someone applied it by hand, or you are looking at the wrong server
There is more difference than expected	Do not run. The excess is someone else's work and it is about to be erased
The object does not exist on the server	Verify the environment; this usually signals the wrong server or the wrong database

The second row is counterintuitive and therefore the most often missed. "No difference" looks like good news, whereas an approved change already applied in production is evidence that the pipeline was bypassed.

Why Tables Are Different

For procedures, views, functions and triggers the comparison can be made directly, because the script contains the object's full body. Tables are a different matter.

Table changes usually carry only the changed part: add a column, drop a constraint, widen a type. Producing the expected full state of a table from the script would require knowing every change applied up to that point, in order. So for tables the comparison is made not against the script but against the definition on the server itself: columns, types, constraints and indexes are read from the catalogue and the two versions are placed side by side.

The practical consequence: if a product says it compares before execution, you should separately ask whether it does so for tables too. The two are technically different paths, and one without the other is not complete.

Rollback Depends on the Same Source

The connection here is usually missed. The rollback script for a change is generated from the object's state before the change. That state is the current definition on the server.

So the source the comparison reads and the source the rollback plan feeds on are the same. Which leads to this: the rollback plan must be generated before execution. If you try to generate it after the change has run, what you get is the new state, which is a copy rather than a rollback plan. The rollback strategy article covers this in detail.

Who Should Look

The person who runs the comparison and the person who reads it can and should be different.

For the approver this is the basis of the approval. If what they see when approving is only the text of the script, the approval was given on incomplete information; what a script will do can only be understood by knowing what it will write over.

For the executor it is the final check. The object may have changed in the time since approval, and only a comparison at execution time catches that.

For the auditor, the record of this comparison matters rather than the comparison itself. "We looked before running" is a claim; the fact that the look is recorded is evidence. The same distinction applies to the question of whether the approved text and the executed text were the same.