Ignore Database Name in Stored procedures, functions, views.
MikeInOr
Posts: 2
I see there is an option to ignore database names in synonyms. Is there any chance of having an option to ignore database names in stored procedures, user defined functions and views?
Background: We scaled out out database arcitecture when we ran into the wall in scaling up. Our scale out strategy involved migrating to multiple SQL servers with subscripted names... i.e. SQL001, SQL002, SQL003... etc.
Each of these servers has multiple databases that are also subscripted... i.e. On SQL001 there are databases dbA001, dbB001, dbC001, etc.
On SQL002 there are databases dbA002, dbB002, dbC002, etc
(This allows us to move customers back and forth between servers and run multiple database sets on a single server).
Several of our existing stored procedures, views and udf's use three part naming to reference across databases... i.e.
Select * from dbA001.dbo.TableX Join dbC001.dbo.TableY
We would like an option to ignore the database name in a 3 part name when comparing these databases. It there any way to accomplish this or is there a chance such functionality might be added? We are currently managing this comparison and deployment with in house code but wopuld love to dump our inhouse code and purchase your tools instead. I understand the proper way to accomplish this is with synonyms BUT I have been over ruled by my boss about implementing synonyms.
Thank you,
Mike.
Background: We scaled out out database arcitecture when we ran into the wall in scaling up. Our scale out strategy involved migrating to multiple SQL servers with subscripted names... i.e. SQL001, SQL002, SQL003... etc.
Each of these servers has multiple databases that are also subscripted... i.e. On SQL001 there are databases dbA001, dbB001, dbC001, etc.
On SQL002 there are databases dbA002, dbB002, dbC002, etc
(This allows us to move customers back and forth between servers and run multiple database sets on a single server).
Several of our existing stored procedures, views and udf's use three part naming to reference across databases... i.e.
Select * from dbA001.dbo.TableX Join dbC001.dbo.TableY
We would like an option to ignore the database name in a 3 part name when comparing these databases. It there any way to accomplish this or is there a chance such functionality might be added? We are currently managing this comparison and deployment with in house code but wopuld love to dump our inhouse code and purchase your tools instead. I understand the proper way to accomplish this is with synonyms BUT I have been over ruled by my boss about implementing synonyms.
Thank you,
Mike.
Comments
I am little confused with the contents of your post.
If you are refering to the full qualification of objects name inside the Stored Procedure Code or Function or View, I suspect the answer will be no.
SQL Compare will not rewrite the code for you. The purpose of SQL Compare is to generate a set of results that show you the differences between objects in each data source and provide a mechanism for deployment or synchronization to make the objects in each data source identical.
If this is a feature that you wish to be added, I recommend that you post on the Red Gate Uservoice Forum using this LINK.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com