Database name as a parameter

Niall

I appreciate the facility to ignore the Database name when object naming includes it in synonyms, but I wonder if you might take it one step further for me?

We have a number of instances on the same server of databases that are grouped together, ie dev, systest and uat environemnts that may include say a warehouse and source. These would be clled dev_warehouse, dev_source for development and uat_warehouse, uat_source for the uat environment and so on. To reduce code complexity and make for cleaner code the ETL code in warhouse uses synonyms to the source database(s) so in the ETL proc the merge would use (on dev) dev_source.table as the source. At the moment when I ship across the new code using sql compare the synonyms in uat get created pointing to dev. It is not too much of a pain to go in and edit these (using the Object Details view on them all then selecting them all, right clicking and creating a drop - create script and using search and replace) however if I or another team member forgets to do this it tend to result in data being cross populated between environments.

What would really help in terms of release quality is if in SQL Compare I ahve the facility to substitute database anmes when included in synonyms, ie replace dev_source with uat_source, doing it via a pattern match would be even better, ie replace dev% with uat_%.

David Atkinson

This is something we've considered, and the likelihood is that if we implement it, it would involve saving the parameters in the SQL Source Control scripts, and substituting when comparing or getting latest based on a locally defined parameter/value pair.

Is this more or less what you had in mind? Can I ask if you're using SQL Source Control or not?

David Atkinson
Product Manager
Red Gate Software