This is a post on moving tables from one schema to another in SQL Server. A semi-rare task for myself, although it could be more frequent for those who use schemas for things like staging tables/versioning.
Creating a new Schema in SQL Server can be done with a simple database scoped one-liner;
CREATE SCHEMA finance AUTHORIZATION dbo
Having dbo as the owner will do the trick in most cases – it avoids having to manage dependency objects.
The ALTER SCHEMA statement is the one of course!… Used to move securables between schemas within the same database in SQL Server.
ALTER SCHEMA finance TRANSFER [dbo].[DimCurrency]
The schema entered in the first part of the command is the schema we are transferring to.
I’ll refresh the Object Explorer Table view and run a select on the table…
(The red squigglies under the table means that the query window hasn’t refreshed yet. Hit CTRL + SHIFT + R to update IntelliSense)
A quick an easy change this is. If it’s a production table, everything hitting the table will need to be updated with the new schema name too.