Change Transfer Schema for all Objects

In my working environment we will be using different schema for production and development environments. Our development apps are configured to Dev schema and Production apps are configured to Prod schema, so when we restore the db from production to development environment, all our dev apps stopped working since the objects securable will be using prod schema. Here I need to change the schema to dev from prod, so the syntax will be like below

ALTER SCHEMA destinationschema TRANSFER sourceschema.objectname

The above syntax can be used change for an object however consider you have N number of objects whose schema need to be changed, in that case you cant manually rewrite the above query for all objects. To overcome this I have written a query to prepare the transfer schema script, you just need to modify the source and destination schema variable values in the script.

Applies to

• SQL Server 2005
• SQL Server 2008 and
• SQL Server 2008 R2

Ho to Use

• Assign values for Source and Destination Schema, please make a note that destination schema should exists in the database
• Execute the script under correct database context
• Copy the output and run it in new query window under correct database context

That’s it you have transferred your securables to new schema

DECLARE @sourceschema VARCHAR(100)
DECLARE @destinationschema VARCHAR(100)
SET @sourceschema = 'sourceschema'
SET @destinationschema = 'destinationschema'
SELECT 'ALTER SCHEMA ' + @destinationschema + ' TRANSFER ' + b.name + '.' + a.name FROM sys.objects a JOIN sys.schemas b
ON a.schema_id=b.schema_id WHERE a.type IN ('U','P','FN','V') AND b.name=@sourceschema