Changing the schema of a table in SQL Server can be a rare task for database administrators but a more common requirement for database developers. This guide walks through how to move a table from one schema to another using the ALTER SCHEMA statement.
During my time as a SQL Database Administrator, assisting with a task like this is a rare event, so it’s great to note some information down.
If you are making this change to a Production SQL Server, you should plan and communicate the change. to ensure all SQL queries that use tables included in this change are updated with the new schema name.
Steps to Change a Table’s Schema
1. Create a New Schema in SQL Server.
Before transferring a table, you may need to create a new schema. I’m running the following SQL command to create a schema named finance in the AdventureWorks database:
-- Create a new schema in MS SQL Server CREATE SCHEMA [finance] AUTHORIZATION dbo;
In most cases, setting dbo as the owner is sufficient. However, experienced developers may prefer an alternative schema owner based on specific project requirements.
2. Transfer Table to the New Schema
To move a table to a different schema, use the ALTER SCHEMA statement. Here’s how to transfer the DimCurrency table from the dbo schema to the finance schema:
-- 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, which is the finance schema in this demo:
3. Verify the Change
After executing the statement, refresh the tables list in SQL Server Management Studio (SSMS) Object Explorer. The table should now appear under the new schema as finance.DimCurrency:
Note: If SSMS shows red squiggly lines under the table name, refresh IntelliSense by pressing CTRL + SHIFT + R to update the query window.
Important Considerations
> Update Queries:
Ensure all SQL queries referencing the moved table are updated with the new schema name.
> Consult Microsoft Documentation:
For guidance on limitations and restrictions when modifying schemas, refer to the official Microsoft Documentation on transferring ownership of a table.
> Large Tables:
When transferring large tables, consider the impact on performance and plan for potential downtime or maintenance windows to minimize disruption.
I hope this guide has helped you out, feel free to check out more of the links you see around here for tips from an experienced SQL Server DBA!
Leave a Reply