When a rebuild would discard state your package never had

🧪

Hands-on lab

Run this module yourself — the `before/` source, the three-engine sandbox, and the worked solution to diff against.

Demos/Learn/course11-module-02 →

Module 1’s refusals were about the operation: moving a table between partition schemes rewrites every row, so it stops.

This module’s refusals are stranger, because the operation is fine. The column change is ordinary, the data would survive, and SchemaSmith stops anyway — over something that has nothing to do with the change you asked for.

The rebuild guard is one rule, not a list

You met this in Course 8: a column reorder needs a rebuild, and a rebuild was refused because Change Data Capture was on.

CDC is one member of a family. Here is a partitioned table refusing the same reorder, on MySQL and MariaDB:

Table rebuild refused for `vault_m2`.`AuditTrail`: the table is partitioned. A rebuild replaces the
table with a shadow copy, and that state lives outside the schema package -- the copy discards it and
no re-deploy can put it back. Move this table with Before/After migration scripts, or clear the
blocking state first and re-run.

Same sentence, different blocking state. And that is the useful realisation: it isn’t a list of special cases that grows every release. It’s one question asked of whatever the engine happens to be holding — Change Data Capture, system versioning, replication, Change Tracking, inheritance, partitioning, whichever the engine in front of it supports.

The question is always the same. A rebuild builds a new table and copies rows into it. So: is everything attached to the old one written down somewhere SchemaSmith can read it back from? Partitioning on the old table isn’t a property of the rows — it’s a property of the object, and the shadow is a different object. CDC’s capture configuration was never in your package at all. Neither can be reconstructed, because neither was ever written down.

Can I rebuild what I drop?

A course made only of refusals teaches you that the tool is timid. It isn’t. Plenty of ordinary changes can only be made by dropping something first — a view that pins a column, a policy that reads it, an index over it. SchemaSmith drops those all the time. Before it does, it asks one question: can I rebuild what I drop?

There are three answers.

1. The package. You scripted the view, or declared the policy or index in the table’s JSON. The recipe is yours, and the deploy rebuilds from it.
2. The catalog. The engine describes the object in full — every predicate, every clause, its enabled state. SchemaSmith scripts it from the server, takes it down and puts it back, whether you declared it or not.
3. Neither. Nobody wrote it down and the server will not say. The deploy stops, names it, and changes nothing.

Every engine fits on those three. Here’s what each looks like.

Where the recipe comes from

SQL Server: the package, when you opt in

SQL Server will not alter a column while a schema-bound view references it. It fails with Msg 4922, which names neither the module nor what to do. Here is what SchemaSmith says instead, at exit 2, with the relevant setting off — which is the default:

Column change blocked by SCHEMABINDING: dbo.vw_AuditActors (blocks [dbo].[AuditTrail].[Actor]).
SQL Server will not alter a column while a schema-bound module references it, and SchemaSmith will not
drop a scripted object it cannot put back. Move the listed module(s) into a schema-bound object folder
(QuenchSlot AfterTablesObjects) and set DropSchemaBoundDependents so the deploy can drop them around
the table work and the after-tables object pass recreates them, or remove SCHEMABINDING from them.

The module, the column it blocks, and both remedies. Now turn it on:

{ "DropSchemaBoundDependents": true }

  Drop schema-bound module dbo.vw_AuditActors - blocks a column change; the after-tables object pass recreates it
  Quenched .\widen-allowed\Templates\Main\SchemaBound Views\dbo.vw_AuditActors.sql

Exit 0. The view was dropped, the column widened, the view rebuilt.

Look at where the view came back from: Quenched … SchemaBound Views\dbo.vw_AuditActors.sql — your package, not a copy of what was on the server. That is the whole basis on which the drop was allowed. SchemaSmith did not save the server’s definition and replay it; it dropped an object it could rebuild from the recipe you gave it.

Which is why placement in the package matters. Schema-bound modules belong in a folder on the AfterTablesObjects slot — the default templates ship SchemaBound Views/ and SchemaBound Functions/, and SchemaTongs writes them there on extraction whether or not you use this setting. A module left in the ordinary Views/ folder is recreated before the table work, which is too early to help.

In the lab, that same deploy carries two more things your package never mentioned. A DBA put a GRANT SELECT on the view, and a row-level security policy on the table:

CREATE SECURITY POLICY dbo.pol_Actor
  ADD FILTER PREDICATE dbo.fn_ActorVisible(Actor) ON dbo.AuditTrail
  WITH (STATE = ON, SCHEMABINDING = ON);

A schema-bound policy holds every column its predicate reads, so it blocks the widening exactly as the view does. And it isn’t in the package. It doesn’t need to be: SQL Server describes a security policy in full, so SchemaSmith scripts it from the catalog, drops it before the column changes, and creates it again after, with its predicates and its state. That’s the second source.

The GRANT comes back too. Your script recreates the view and says nothing about who may read it, so SchemaSmith captures the view’s permissions before the drop and re-applies them once the view exists again. The run manifest records that as a reapplied row. After the deploy the lab checks all three — Actor at 128, the policy enabled and schema-bound, one GRANT on the view.

SQL Server: a changed function needs no opt-in

DropSchemaBoundDependents exists for a column change. A changed function is the other half of the same problem: SQL Server will not alter dbo.fn_ActorKey while a schema-bound view calls it. Here the deploy needs no setting at all. The view is in your package, so SchemaSmith drops it, applies the new function body and puts the view back in the same run.

That’s the line between opt-in and default. A column change under a schema-bound module asks you to say “yes, cycle my modules”, because the module has to be in the right folder for the rebuild to work. A changed module whose dependents you declared is unambiguous — the package holds every recipe involved.

PostgreSQL: the package by default, the catalog for triggers

PostgreSQL has the same lock without any opt-in to manage it. It will not change a column’s type while a view, a materialized view, a SQL-standard routine or a row-level security policy reads the column. The lab’s table declares its policy in JSON:

"RowLevelSecurity": true,
"Policies": [ { "Name": "p_actor_present", "UsingExpression": "actor <> ''" } ]

and vw_audit_actors sits in an ordinary Views/ folder. Then the DBA adds something the package never mentions — an audit trigger that fires only when actor changes:

CREATE TRIGGER trg_actor_changed BEFORE UPDATE OF actor ON public.audit_trail
  FOR EACH ROW WHEN (NEW.actor IS DISTINCT FROM OLD.actor)
  EXECUTE FUNCTION public.fn_actor_changed();

Widen actor from varchar(64) to varchar(128), no setting. Exit 0. The policy, the trigger and the view were each taken down before the type change and put back after it — the view by its script, the policy by the table pass, and the trigger, still enabled, from the catalog. A trigger’s WHEN clause and UPDATE OF list are fully described by PostgreSQL, so it needs no declaring. A publication’s row filter over the column (PostgreSQL 15 and later) is handled the same way.

MySQL: a rename needs a recipe

On MySQL the change that needs a recipe is a rename. MySQL refuses to rename a column a functional index reads. The lab deploys a plain Contact table, then a DBA adds a case-insensitive lookup index the package doesn’t mention:

CREATE INDEX ix_dba_email ON Contact ((LOWER(Email)));

Redeploy and the index is untouched. DropUnknownIndexes defaults to false, so an index the package doesn’t mention is not the package’s business. Hold on to that — it decides what happens in the next section, when the package renames Email.

MariaDB: nothing to rebuild

MariaDB carries functional and generated-column dependents across a rename by itself, so there’s nothing for SchemaSmith to rebuild here. Its refusal comes below, beside MySQL’s: a foreign key nobody declared.

Three sources, one table

Source	SQL Server	PostgreSQL	MySQL
The package	schema-bound views and functions in the after-tables folders — with DropSchemaBoundDependents for a column change, by default for a changed function	declared views, routines, materialized views and policies	declared functional indexes and generated columns
The catalog	a schema-bound security policy	a trigger’s WHEN / UPDATE OF, a publication’s row filter	—
Neither	refused before the drop	refused before the drop	refused before the rename, or before a foreign key is dropped (MariaDB too)

Neither: refused before the drop

SQL Server: a view nobody declared

Start again from NVARCHAR(64), add a second schema-bound view by hand — dbo.vw_DbaActors, in no package — and run the same widening, setting on:

Column change blocked by SCHEMABINDING: dbo.vw_DbaActors, which the package does not declare, so
nothing in this run could put it back and SchemaSmith will not drop it. Add its script to a
schema-bound object folder (QuenchSlot AfterTablesObjects), or remove SCHEMABINDING from it.
Nothing was changed.

Exit 2. Actor is still 64, and both views are still there — including vw_AuditActors, which the package could have rebuilt. The refusal fires before the deploy drops anything for this change. It doesn’t drop the one it can put back and then discover the one it can’t.

You turned on a setting that says “drop my schema-bound dependents”. SchemaSmith reads that as “drop the ones I can build again”, which is the only reading under which the setting is safe.

SQL Server: an encrypted view

Now make the package’s own view WITH ENCRYPTION, and run the widening again:

Column change blocked by an ENCRYPTED schema-bound module: dbo.vw_AuditActors. Its definition cannot
be read from the server, so nothing can recreate it once dropped -- DropSchemaBoundDependents
deliberately does not extend to it. Remove SCHEMABINDING or the encryption from the listed module(s).

Exit 2. Nothing dropped.

OBJECT_DEFINITION returns NULL for an encrypted module. The server will not hand the text back to SchemaSmith, to SchemaTongs, or to you. So the guarantee the entire feature rests on — the after-tables pass will recreate it — cannot be made, and the moment it cannot be made, your opt-in stops applying.

That is worth sitting with. An opt-in that quietly became destructive in an edge case would be worse than no opt-in at all — because you’d have stopped reading the deploy log.

PostgreSQL: a tool that learned to refuse

Start again from varchar(64) and add a report view the package knows nothing about:

CREATE VIEW public.vw_dba_report AS SELECT actor FROM public.audit_trail;

Widen actor again:

A column type change in this package has to drop and re-create what reads that column, and the
package does not declare: view public.vw_dba_report. Declare it in the package so it can be put back,
or drop it in a Before script. Nothing was changed.

Exit 2. actor is still varchar(64), both views are still there.

This refusal is new, and that’s what makes it the clearest example in the course. Before it, the same deploy dropped vw_dba_report to make room for the type change and nothing put it back. A green deploy, minus a view somebody’s report depended on. Nothing about the change was different — only the answer to can I rebuild what I drop? got checked.

MySQL: declare it to adopt it

Back to the DBA’s ix_dba_email. The package renames Email to Mail and still says nothing about the index. Exit 2, and the log names it:

  A column rename would break index Contact.ix_dba_email, which the package does not declare. Declare it in the table's JSON, or drop it in a Before script.

Nothing renamed, index intact. Notice the symmetry. The index survived every earlier deploy because it was unmanaged. For exactly the same reason, SchemaSmith can’t rebuild it over a new column name — it has no recipe.

So write one down. Add the index to the table’s JSON, over the new name:

{ "Name": "ix_dba_email", "IndexColumns": "(lower(`Mail`))" }

Exit 0. The deploy drops the index ahead of the rename and builds it again over Mail. Declaring it adopted it. That’s Module 4’s rule arriving early: unset means unmanaged, and unmanaged is something SchemaSmith won’t destroy on your behalf.

MySQL and MariaDB: the foreign key a DBA added

Same question, different object. A package declares Account and Payment, but not the foreign key between them; somebody added that by hand. Now the package moves both code columns to utf8mb4_bin. Neither engine changes a collation under a foreign key, so the key has to come off first, and SchemaSmith can only put back a key it has a recipe for:

  Foreign key Payment.fk_dba_payment_account blocks a column type or collation change and is not in the package, so it would be dropped and not put back. Declare it in the package, or drop it before the deploy.

Exit 2, the key still there, no column changed. Declare the key in Payment.json and the next deploy takes it down, changes both columns, and puts it back: Drop FK for column collation change, two Modify column lines, Create FK: Payment.fk_dba_payment_account. Recipe in hand, the answer flips.

Check yourself: Your team turns on DropSchemaBoundDependents to unblock a column change on SQL Server. A reporting service reads the schema-bound view through a GRANT that lives in no script in the package. After the deploy, does the reporting service still have access? And what would make SchemaSmith refuse the deploy instead?

Yes. The view is dropped and recreated from your package script, and the script carries no GRANT — but SchemaSmith captures the view’s permissions before the drop and re-applies them once the view exists again. The run manifest shows a reapplied row for it. If the run drops a view for a column change and does not re-create it, it warns that the grants were not put back, rather than losing them quietly.

The refusal comes from the other half of the question. If the column is also read by a schema-bound view the package does not declare, there is no recipe for that one, so the deploy stops by name at exit 2 before dropping anything — the declared view included. Same if the view is encrypted: the server won’t give the definition back, so nothing can rebuild it.

The general shape: what is in your package comes back from the package; what the engine describes in full — a grant, a security policy, a trigger — comes back from the catalog; anything else is refused before the drop.

---

Three postures toward one table, in one module. SchemaSmith converged the column when nothing stood in the way. It refused when a rebuild would have discarded state nobody wrote down. And in between it acted — dropping and rebuilding dependents it could put back.

That middle case is what makes the other two coherent. The tool isn’t refusing out of caution. It answers one checkable question every time: can I rebuild what I drop? The package has the recipe, or the catalog does, or neither does. Two of those answers get the work done without being asked twice. The third stops the deploy and tells you exactly what to write down.

Next: Module 3 — the refusal you did not ask for, where the engine would have done exactly what your package said, and SchemaSmith declines anyway.

Until then, may everything you drop be something you can build again.

— Forge

Mark this lesson complete