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?
Create Schema [LIFE]
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 '
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