How to Change Schema of an Object(Table,View,Stored Procedure) in SQL Server Database - SQL Server / TSQL Tutorial Part 28

Scenario:
You are working as SQL Server developer in Life Insurance Company. They have Database Name TechBrothersIT and one of the Schema name the objects are using is TB. They have given you assignment to Rename the Schema to LIFE.How would you do that?


Solution:

There is no straight forward way to rename a schema in SQL Server Database. We have to follow below steps to make this change.

Step 1: Create a New Schema
Create a new Schema with name LIFE, we can use below script.

Create Schema [LIFE]



Step 2: Transfer the Objects to New Schema. 
We can use below script to transfer single object(table,view,Stored Procedure etc.) to new schema.

Use Database
ALTER SCHEMA NewSchema
TRANSFER OldSchema.ObjectName
GO

Let's say I have a table in TB Schema and I can use below script to move to Life Schema.

Alter Schema [LIFE] Transfer TB.[test] ;


The above query has to be repeated for each of the object to transfer from TB schema to LIFE schema. We can use below select query to generate our Alter query for other objects in a database to transfer objects from one schema to another.

Declare @SourceSchema VARCHAR(100)
Declare @DestinationSchema VARCHAR(100)
SET @SourceSchema='TB'
SET @DestinationSchema='LIFE'
Select 'Alter Schema ['+@DestinationSchema+'] Transfer '
+@SourceSchema+'.['+name+']' 
from sys.objects
where schema_name(schema_id)=@SourceSchema


Provide the @SourceSchema and @DestinationSchema variable values and query will generate Alter statements for you. Copy and run in your Query window. 

Note: Make sure you test the changes in development environment before you run in UAT and Production environments.



Video Demo: How to Transfer objects from one schema to other in SQL Server

1 comment:

  1. Changing the schema of an object can be a difficult task, but it doesn't have to be. With the help of a university assignment helper, you can easily go about changing the schema of an object. They can provide you with the necessary advice and guidance to ensure that you are successful in your task. With their help, you can ensure that your object has the right schema to meet your requirements.

    ReplyDelete