Microsoft SQL Server 2005/2008:Migrating Data from One Schema to Another.

Use this script to transfer data from one Schema(Transfer_Schema) to Another(DBO).

Read Article for your reference and knowledge,http://msdn.microsoft.com/en-us/library/ms173423.aspx

Case 1.Transfer Data from DBO to Transfer_Schema.

Declare @Object_Name varchar(100) --Database Objects

Declare @Object_Type varchar(100)--Object Type

Declare @SQL varchar(200)--Script for SQL Query

DECLARE db_cursor CURSOR FOR

select name,type from sys.sysobjects
where type in ('U','SQ','F','D','P','V','TR','IT','TF','FN','K')
order by 2

OPEN db_cursor 

FETCH NEXT FROM db_cursor INTO @Object_Name, @Object_Type

WHILE @@FETCH_STATUS = 0 

BEGIN 

--Execute this script for object Transfer
SET @SQL='ALTER SCHEMA Transfer_Schema TRANSFER  [dbo].['+@Object_Name +']'

EXEC(@SQL)

Print 'Object Transfered :'+@Object_Name


FETCH NEXT FROM db_cursor INTO @Object_Name, @Object_Type

END

Close db_cursor

Deallocate db_cursor

Case 2.Transfer Data from Trasfer_Schema to DBO.

Declare @Object_Name varchar(100) --Database Objects

Declare @Object_Type varchar(100)--Object Type

Declare @SQL varchar(200)--Script for SQL Query

DECLARE db_cursor CURSOR FOR

select --OBJECT_SCHEMA_NAME(id) Schema_Name,
name,type from sys.sysobjects
where type in ('U','SQ','F','D','P','V','TR','IT','TF','FN','K')
order by 2

OPEN db_cursor 

FETCH NEXT FROM db_cursor INTO @Object_Name, @Object_Type

WHILE @@FETCH_STATUS = 0 

BEGIN 

--Execute this script for object Transfer

SET @SQL='ALTER SCHEMA dbo TRANSFER [Transfer_Schema].['+@Object_Name+']'

EXEC(@SQL)

FETCH NEXT FROM db_cursor INTO @Object_Name, @Object_Type

END

Close db_cursor

deallocate db_cursor

Re Execute if any error and check all objects migrated to new Schema.

About Me