Transfer Schema Objects

Problem: How to transfer objects from an old schema to a new schema with in the database.

Solution: ALTER SCHEMA TRANSFER

This transfers the specific objects in between schemas.

Syntax:

ALTER SCHEMA schema_name  

   TRANSFER [ <entity_type> :: ] securable_name  

[;] 

<entity_type> ::= 

    { 

    Object | Type | XML Schema Collection 

    } 

Example: In this example, I would like to show we can move tables, views and stored procedures from an old schema to new schema.

Declare @NewSchema VARCHAR(256),

@OldSchema VARCHAR(256)

SET @NewSchema='NewSchema'

SET @OldSchema ='OldSchema'

SELECT 'ALTER SCHEMA '+@NewSchema+'  TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];'

FROM sys.Objects DbObjects

INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id

WHERE SysSchemas.Name = @OldSchema

AND (DbObjects.Type IN ('U', 'P', 'V'))--U(table)|P(procedures)|V(views)