powershelldba.de

Moving a Table to Another Schema in SQL Server

Moving a table between schemas looks like a one-line command — and mostly it is. But it silently changes effective permissions, breaks anything hardcoded to the old three-part name, and can leave dependent views and procedures broken until you rebuild them. Here's how to do it without surprises.

The Command

Schema moves are metadata-only operations — no data is copied, no pages are rewritten. That's the whole point of doing it this way rather than a manual copy-and-drop:

ALTER SCHEMA sales TRANSFER dbo.Orders;

-- The table is now sales.Orders — same object, same object_id, same data pages,
-- just re-parented to a different schema. This is instantaneous regardless of table size.

Because it's metadata-only, it takes a schema modification lock briefly and completes in milliseconds even on a billion-row table. That speed is exactly why people underestimate how much else needs to change around it.

What Silently Breaks

1. Permissions Do Not Carry Over

This is the one that causes production incidents. Permissions in SQL Server can be granted at the schema level, and a table moved into a new schema inherits that schema's permission structure — not the permissions it had before.

⚠ A table moved out of a schema where an application role had SELECT access, into a schema where that role has no grant, becomes invisible to the application instantly — with no error until the application actually tries to query it. Always check schema-level grants on both the source and destination schema before moving anything used by a running application. 
-- Check schema-level permissions before you move anything
SELECT
    pr.name          AS principal_name,
    pe.permission_name,
    pe.state_desc,
    s.name            AS schema_name
FROM sys.database_permissions AS pe
JOIN sys.database_principals AS pr ON pr.principal_id = pe.grantee_principal_id
JOIN sys.schemas            AS s  ON s.schema_id      = pe.major_id
WHERE pe.class = 3  -- 3 = SCHEMA
  AND s.name IN ('dbo', 'sales');   -- source and destination schema

Object-level (table-specific) grants, if any exist directly on the table rather than inherited from the schema, do move with the table. It's the schema-level inheritance that changes — check both.

2. Anything Hardcoding the Old Three-Part Name

Application connection strings, linked server queries, SSIS packages, Reporting Services datasets, and any dynamic SQL that builds dbo.Orders as a literal string will fail to find the table at its old address. ALTER SCHEMA TRANSFER does not leave a synonym or an alias behind automatically.

Bridge the gap with a synonym if you can't update every caller at once: CREATE SYNONYM dbo.Orders FOR sales.Orders; lets old code keep working under the old name while you migrate callers on your own schedule, then drop the synonym once nothing references it.

3. Dependent Views, Procedures, and Functions

Objects that reference the table by its old two-part name (dbo.Orders) inside their definition are not automatically rewritten. They keep working only because SQL Server resolves the two-part name at execution time and, in most cases, will simply fail once the object under the old name no longer exists (unless you left a synonym in place).

-- Find every object that references the table BEFORE you move it
SELECT DISTINCT
    o.name                              AS referencing_object,
    o.type_desc
FROM sys.sql_expression_dependencies AS d
JOIN sys.objects                    AS o ON o.object_id = d.referencing_id
WHERE d.referenced_entity_name = 'Orders'
  AND OBJECT_SCHEMA_NAME(d.referencing_id) IS NOT NULL;

Every object this query returns needs either a synonym bridging the old name, or a deliberate update to its definition referencing the new schema — planned as part of the same change, not discovered afterward from an error log.

4. Foreign Keys, Indexes, and Constraints

These move with the table intact — foreign keys, primary keys, check constraints, indexes, and triggers all remain attached and functional. This is one of the genuine advantages of ALTER SCHEMA TRANSFER over a manual rebuild: you don't have to recreate any of it.

5. Statistics and Query Plans

Column and index statistics survive the move. However, any cached execution plan that referenced the table under its old schema name is invalidated and will recompile on next use — expected, and generally not something to worry about, but worth knowing if you're diagnosing a sudden compile spike right after a schema change window.

A Safe Move Checklist

  1. Inventory dependents — run the sys.sql_expression_dependencies query above, and separately grep application code and SSIS/SSRS artifacts for the old two-part name.
  2. Compare schema-level permissions between source and destination — grant whatever is missing on the destination schema before the move, not after.
  3. Decide on a synonym bridge for anything you can't update in the same change window.
  4. Run the transfer — it's fast, but do it in a maintenance window if the table is large and hot, since it does take a brief schema-modification lock that can queue behind other activity.
  5. Verify access immediately — run a representative query as the application's service account, not as yourself, since your own permissions are almost certainly broader.
  6. Recompile or rebuild dependent objects that referenced the old schema name explicitly in their definitions.

The Bottom Line

ALTER SCHEMA TRANSFER is fast and safe for the data itself — it's a metadata change, not a data move. The risk is entirely in what surrounds the table: schema-level permissions that don't carry over, hardcoded references that don't get rewritten, and dependent objects that need a synonym or an explicit update. Inventory those three things before you run the command, and the move really is a one-liner.

← Back to Blog