I need to run a migration from my server1 to server2, in both servers have the same DB schema
Server1: DB1.dbo... All tables DB2.dbo... All tables
Server2: DB1.dbo... All tables DB2.dbo... All tables
I need connect from server1 to server2, and also until I finished this script, I have to run on Local environment and the DB names are different (like DB1_Local instead of DB1)
I try to create a synonym for "Server1.DB1.dbo" so when I have to join a table of Server1 with other on Server2 I'll run
MySynonymForServer1.MyTable JOIN MySynonymForServer2.MyTable ON ....
But this doesn't work...
Is there any way to accomplish this?
Synonyms are for objects. You cannot create a synonym for a schema or a database. However, you can create a synonym fo an object in a different database. so in your case, you have to create a synonym for every object in the database. You could automate that by looping through the objects in sys.objects. Just make sure to filter out unwanted object types like constraints.