Schema Level Refresh in MS SQLServer

Consider a Scenario like, we have two environments Production and Development where having same set of tables with two different schemas based on the environment. So the application is working based on the Schema .

The Schema names are Prod ,Dev and the default schema dbo.

we are doing a Database Refresh activity from Production to Development as a normal DBA practice but after that the application is throwing error like 

                                       “Not able to connect or invalid object exist “

Reason: 

After the Database Restore activity, the Development Database will be replaced with Production Database in the object level also , Dev objects  have been replaced with Prod objects hence DEV application is not able to identify the changed objects having production schema.

In this Scenario, we need to check the objects where any schema level change is required or not after doing the DB Refresh.

Before the Database refresh activity from Production to Development

                                                            Production Database

                                                        

Developmet Database

So After the Database Refresh activity the Development database schema will be changed into production.

So we need to do a Schema level refresh also to make the application fully functional.

Below are the script to use a schema level refresh activity.

declare @sql varchar(8000), @table varchar(1000), @oldschema varchar(1000), @newschema   varchar(1000)

  set @oldschema = 'dbo'
  set @newschema = 'exe'

 while exists(select * from sys.tables where schema_name(schema_id) = @oldschema)

  begin
      select @table = name from sys.tables 
      where object_id in(select min(object_id) from sys.tables where  schema_name(schema_id)  = @oldschema)

    set @sql = 'alter schema ' + @newschema + ' transfer ' + @oldschema + '.' + @table

   exec(@sql)
 end

After the Schema level Change you should see the table like this

If you want to move all tables into a new schema, you can use the undocumented and to be deprecated at some point, sp_MSforeachtable stored procedure:

exec sp_MSforeachtable "ALTER SCHEMA TargetSchema TRANSFER ?"