This is a post on how-to on changing the schema of a table in SQL Server, moving it from one schema to another using the ALTER SCHEMA [tableName] TRANSFER SQL statement.
During my time as a SQL Database Administrator, assisting with a task like this is a rare event. If you are more of a Database Developer, then you’ll probably need to do this more often.
To change the schema of a table in MSSQL, run through the following:
# Create New Schema in SQL Server
# Change Schema for a Table in SQL Server
If you are making this change to a Production SQL Server, you should ensure all SQL queries that use tables included in this change are updated with the new schema name.
Create New Schema SQL Server
When performing changes in SQL Server, we should search for the relevant Microsoft Documentation on the subject. In this case, see Create Database Schema: Limitations and Restrictions
The SQL below will create a new schema within the [Adventureworks] database which will be the new schema we are migrating the test table to.
-- Create a new schema in MS SQL Server CREATE SCHEMA [finance] AUTHORIZATION dbo;
Setting dbo as the owner will be fine in most cases. If you have experienced SQL Database Developers around you, they might ask for this schema to be created with an alternative schema owner.
We have our new schema created, now let’s move on to the next part for migrating a table to this schema.
Change Schema for a Table in SQL Server
To change the schema of a table in SQL Server, we use the ALTER SCHEMA <db> TRANSFER statement.
MS Docs has some examples for Transferring the ownership of a table too. They call it ‘transferring a securable‘.
-- Change schema of table in SQL Server ALTER SCHEMA finance TRANSFER [dbo].[DimCurrency]
The schema name in the left-most part of the SQL command is the schema we are transferring to (finance).
Now that I’ve created the new Schema, I’ll refresh the tables within the SSMS Object Explorer. I see the table has changed from dbo.DimCurrency to finance.DimCurrency.
(Note: the red squigglies under the table name in the SSMS query window mean that the query hasn’t been refreshed yet. Hit CTRL + SHIFT + R to update SSMS IntelliSense)
Remember to now update all queries, and change the schema name when querying the transferred table.
I hope this guide has helped you amend the schema of a table in SQL today. If you like this post and want more random tips from an MS SQL DBA, check out my latest posts on my DBA Blog Homepage.
Leave a Reply