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
?"
