I have two development databases locally
Alpha
Beta_Dev
I have two production databases remotely
Alpha
Beta
I use SQL Compare to sync the schema of the two Alpha databases.
Sometimes, stored procedures in the Development version of Database Alpha
reference tables in Database Beta
, or in the local environment, Beta_Dev
.
For Example:
Select * from Beta_Dev.dbo.MyTable
This code will not work on the production server, because the Beta Database is called Beta
, rather than Beta_Dev
.
Is there a way, using SQL Compare, to have all SP's or Views be compared such that Beta_Dev (local) = Beta (remote)
?
Not only would the databases be uploaded in working order, but SQL Compare won't be tricked into thinking the SP's are different, when only Beta_Dev / Beta are different.
I don't think you can do it the way you want but here is a potential workaround if it is suitable for what you want to do
Create Synonyms
for the objects that are reference in the Beta
databases
USE [Alpha]
CREATE SYNONYM [dbo].[BetaMyTable] FOR [Beta].[dbo].[MyTable]
and
USE [Alpha_dev]
CREATE SYNONYM [dbo].[BetaMyTable] FOR [Beta_dev].[dbo].[MyTable]
so your procs can now use:
SELECT * FROM BetaMyTable
in SQL Compare, there is an ignore rule option in Edit Project... -> Options
called Database and server name in synonyms
. I believe it is checked by default but it is there if you want to change it