SQL SERVER – Move a Table From One Schema to Another Schema

ALTER SCHEMA can Move a Table between schemas within one database. A customer had imported under the wrong schema.

-- Reviewed change template in the intended database:
-- ALTER SCHEMA NewSchema TRANSFER OldSchema.TableName;
SELECT s.name AS SchemaName, t.name AS TableName
FROM sys.tables t JOIN sys.schemas s ON s.schema_id=t.schema_id
ORDER BY s.name,t.name;

The destination schema must exist. The operator needs documented object and destination permissions. This changes the metadata container. It does not move data into another database or file.

References to OldSchema.TableName are not rewritten automatically. Inspect application SQL, procedures, views, synonyms and schema-bound dependencies. Transfer drops object-associated permissions. Preserve and deliberately restore required grants while assessing schema-level access.

Capture the original schema, dependencies and permissions. Test on a copy and validate the application afterward. Reversing the transfer alone does not necessarily restore the original access behavior.

Reference: Schema transfer references and permissions.

Related reading

• Comprehensive Database Performance Health Check
• Pinal Dave on YouTube
• Count Table in Cache – SQL in Sixty Seconds #149
• List All Sessions – SQL in Sixty Seconds #148
• Line Numbers for SSMS Efficiency – SQL in Sixty Seconds #147
• Slow Running Query – SQL in Sixty Seconds #146
• Change Database and Table Collation – SQL in Sixty Seconds #145
• Infinite Loop – SQL in Sixty Seconds #144
• Efficiency Trick – Query Shortcut – SQL in Sixty Seconds #143
• SQL SERVER – 16 CPU vs 1 CPU : Performance Comparison – SQL in Sixty Seconds #142
• SQL SERVER – TOP and DISTINCT – Epic Confusion – SQL in Sixty Seconds #141

A schema transfer is not an automatic reference repair, it is a qualified-name change with dependency and permission effects.

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