Recently I created a table under a wrong schema. Later, I realized the problem and moved the table to the correct schema. Here, I have couple of sample script for moving table to another schema.
Moving one table to a schema
For transferring one table to another schema is by using ALTER SCHEMA transact SQL statement. Here is a sample script to transfer a table PurchaseOrder from its default dbo schema to a new schema Purchasing.
ALTER SCHEMA Purchasing
TRANSFER dbo.PurchaseOrders;
GO
Moving all tables to a schema
For moving all the tables to another schema at the same time then use the SP_MSFOREACHTABLE stored procedure. Here is the sample script.
EXEC SP_MSFOREACHTABLE "ALTER SCHEMA Purchasing TRANSFER ?"
Reference
- More about TRANSFER SCHEMA T-SQL statement at Microsoft Docs.