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.