Menu & Search
Changing Schemas in SQL Server

Changing Schemas in SQL Server

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. 

There’s 2 parts to this one;
# Creating a new schema.
# Changing schemas.

I’d absolutely recommend having a read of this post by Steve Jones on this stuff if you want more info… sqlservercertal.com is seriously an amazing resource for the SQL Server community!


Create a New Schema

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.

New Schema SQL Server

Changing (Altering) Schemas

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.

Alter Schema SQL Server

I’ll refresh the Object Explorer Table view and run a select on the table…

Alter Schema Transfer SQL Server

(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.