Applies to SQL Server 2019, 2022 and 2025.

ALTER SCHEMA TRANSFER moves a table, view, type or other schema-scoped object from one schema to another in the same database. It’s a metadata change, so it’s quick even on a huge table, and I use it a lot for reloads: build the new copy of a table in a staging schema, then swap it in. It has one habit that catches people out, though, and it’s worth knowing before you run it in production.

How ALTER SCHEMA TRANSFER works

The syntax names the schema you’re moving the object to, then the object you’re moving:

ALTER SCHEMA Archive TRANSFER dbo.Orders;              -- a table (OBJECT is the default)
ALTER SCHEMA Archive TRANSFER TYPE::dbo.PhoneNumber;  -- a user-defined type

A few things to know:

• It only works within one database. There’s no cross-database move.
• No data moves. The object keeps its object_id, and a table takes its indexes, constraints and triggers with it.
• The target schema must already exist, and it can’t already hold an object with the same name.
• You need CONTROL on the object and ALTER on the target schema.
• It needs a lock on the object, so on a busy table it waits behind running queries like any other schema change. My post on schema locks and online index maintenance covers why.

A worked example: the schema swap

This builds a small table with a permission on it, loads a fresh copy in a Stage schema, and swaps the two inside one transaction so readers never see a half-loaded table.

CREATE SCHEMA Stage;
GO
CREATE SCHEMA Retired;
GO
CREATE USER ReportReader WITHOUT LOGIN;
GO

CREATE TABLE dbo.Customer
(
    CustomerID   int          NOT NULL PRIMARY KEY,
    CustomerName nvarchar(100) NOT NULL
);
INSERT dbo.Customer VALUES (1, N'Old data');
GRANT SELECT ON dbo.Customer TO ReportReader;

-- Build the replacement
CREATE TABLE Stage.Customer
(
    CustomerID   int          NOT NULL PRIMARY KEY,
    CustomerName nvarchar(100) NOT NULL
);
INSERT Stage.Customer VALUES (1, N'New data'), (2, N'Another customer');

-- Swap: old copy out, new copy in
BEGIN TRANSACTION;
    ALTER SCHEMA Retired TRANSFER dbo.Customer;
    ALTER SCHEMA dbo     TRANSFER Stage.Customer;
COMMIT TRANSACTION;

SELECT CustomerID, CustomerName FROM dbo.Customer;  -- the new rows

That works, and the swap itself takes a moment. Now run this:

SELECT pr.name                         AS GranteeName,
       pe.permission_name              AS PermissionName,
       OBJECT_SCHEMA_NAME(pe.major_id) AS SchemaName,
       OBJECT_NAME(pe.major_id)        AS ObjectName
FROM sys.database_permissions AS pe
JOIN sys.database_principals  AS pr
  ON pr.principal_id = pe.grantee_principal_id
WHERE pe.class = 1
  AND pr.name = N'ReportReader';

It returns nothing. ReportReader can’t read dbo.Customer any more.

The common mistake

The mistake is assuming a transferred object keeps its permissions. It doesn’t. Every permission granted on the object is dropped when it moves. In the swap above there are really two losses: the GRANT on the old table vanished as it went to Retired, and the new table never had one in the first place. The first anyone hears of it is a report failing with a permissions error the next morning.

There are two ways I deal with it:

• Script the grants and re-apply them straight after the transfer, inside the same transaction.
• Better, grant at schema level so there’s nothing on the object to lose: GRANT SELECT ON SCHEMA::dbo TO ReportReader;. A table moved into dbo picks that up straight away.

The second mistake is moving code rather than tables. If you transfer a stored procedure, view, function or trigger, the text stored in sys.sql_modules still contains the old schema name, so OBJECT_DEFINITION and any scripting tool show the wrong thing. Microsoft’s advice is to drop and re-create modules in the new schema instead, and I follow it.

Watch out for

• References aren’t updated. Views, procedures and synonyms that use the old two-part name will break. Check sys.sql_expression_dependencies for the object before you move it.
• One-part names. If you write TRANSFER Customer, SQL Server resolves it through your default schema first. Always use the two-part name.
• Constraint names. Constraints are schema-scoped objects and move with the table. If both copies have a primary key called PK_Customer, move the old table out before moving the new one in, or the second transfer fails.
• Ownership. An owner set explicitly on the object stays the same. Otherwise the object is owned by whoever owns the new schema, which can change ownership chaining.
• Blocking. The swap is fast, but it waits for running queries on the table, and new queries queue behind it while it waits.

Where it fits

I mostly use ALTER SCHEMA TRANSFER for table swaps after a full reload, and for tidying old tables into an Archive schema before I drop them. For anything with code in it, I script it out and re-create it. There’s more on day-to-day DBA tasks in the SQL Server DBA Handbook, and the full syntax is on Microsoft Learn under ALTER SCHEMA (Transact-SQL).

This topic came up in the SQLServerCentral Question of the Day: Switching Schemas I.

Clive Roberts DBA