In SQL Server, if you ever need to move one or more tables to another schema, you can use the T-SQL ALTER SCHEMA command.

Example

The following code creates a new schema called Sales and then moves the dbo.Customers table into it:

CREATE SCHEMA Sales;
GO
ALTER SCHEMA Sales TRANSFER dbo.Customers;
GO

Result:

15:51:33 Started executing query at Line 9
Commands completed successfully.
15:51:33 Started executing query at Line 11
Commands completed successfully.
Total execution time: 00:00:00.025

The table has now been moved to the new schema.

We can reference this table by using Sales.Customers. For example:

SELECT * FROM Sales.Customers;

Move Multiple Tables to a New Schema

We can transfer multiple tables to a different schema by running multiple ALTER SCHEMA statements.

Let’s move two more tables into our new schema:

ALTER SCHEMA Sales TRANSFER dbo.Orders;
ALTER SCHEMA Sales TRANSFER dbo.OrderItems;

Result:

Commands completed successfully.

Now we can query those two tables by qualifying them with Sales:

SELECT * FROM Sales.Orders;
SELECT * FROM Sales.OrderItems;

Move All Tables to a New Schema

We can use the (undocumented) sp_MSforeachtable procedure to move all tables to a new schema:

EXEC sp_MSforeachtable "ALTER SCHEMA Sales TRANSFER ?";

Result:

Commands completed successfully.

Running that code transferred all tables in my database to the Sales schema, regardless of which schema they previously belonged to.