SQL SERVER – How to Move a Table into a Schema in T-SQL

In the world of SQL Server, schemas provide a useful way to segment your database into logical, manageable sections. If you’ve ever wondered how to move a table from one schema to another using T-SQL, this post is for you.

Transferring a Table to a New Schema

T-SQL provides a straightforward command to move a single table into a different schema. The syntax is as follows:

ALTER SCHEMA TargetSchema 
    TRANSFER SourceSchema.TableName;

You replace TargetSchema with the name of the schema you want to transfer the table to, and SourceSchema.TableName with the current schema and name of the table you’re moving.

Moving Multiple Tables into a New Schema

If you have a requirement to transfer multiple tables into a new schema, SQL Server has an undocumented stored procedure sp_MSforeachtable which you can use:

exec sp_MSforeachtable "ALTER SCHEMA TargetSchema TRANSFER ?"

Again, replace TargetSchema with the name of the schema you want to move your tables to.

Please note, this stored procedure is undocumented and it’s advised to use it with caution as it may be deprecated in future versions.

Data Integrity During Transfer

One of the concerns when moving tables between schemas might be regarding the data within the tables. Will it be affected by the transfer? The short answer is: No.

When a table is moved to another schema, the data remains intact. This operation merely alters the logical grouping and permissions associated with the table, not the data itself.

Wrapping Up

Moving tables between schemas is a common task when you’re reorganizing your SQL Server database. It helps to keep your database structured and easier to manage. And with T-SQL, the task becomes even simpler.

Remember, it’s always a good practice to take a backup before performing such operations to ensure you can restore your database in case of any unexpected issues.

You can further follow me on Twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)