Comments
9 comments
-
Hi @gbritton,
Thanks for your suggestion.
Can you please kindly cast your vote on this user voice post? -
I'd like too, but I'm out of votes
-
Hi @gbritton
Just to let you know we are looking at allocating more votes to you or other options. -
Hi @gbritton
You should be able to vote now. Please give it a try and let us know if there is any issue. -
Just a suggestion but I get round a similar problem by using synonyms. if i need a separate version of my codebase to point to a separate dataset then i will set up MyGoodData_ab and MyGoodData_cd wherever there are but have the realname of the object as a synonym pointing to the dataset i want to use. When i then run a code comparison on the proc i will therefore pick up only logic changes.
NB: do need to keep track of what synonyms you have set up and only deviate where they point on non-production databases -
@kalo have you used this to synonym system views? does that work? Even so, it doesn't solve my problem, which is to exclude search results where the only difference is some text that I don't care about.
-
No i don't think you can synonym a system object but you could encapsulate that reference in your own view. When your developer changed wanted to change the particular dataset he was referencing locally he could then amend the local view only rather than the procedure : therefore code comparison of the procedure would show only the code differences you wanted to focus on.( MyStaging.sys.tables ) but instead referenced your own view (MyStagingSysTables as select * from mystaging.sys.tables) then when the developer wanted to repoint locally they wouldn't change the proc they would change the view (of course, this would mean all other objects referencing the view would be repointed too, but this would be only on their local db)
I now try and adhere to the rule of not explicitly referencing a separate database from my codebase - especially now that SQL Prompt code analysis flags when i do - and always make cross database references via synonyms.
So in your example if your production code in the first place didn't have the cross db reference
This doesn't solve the exact issue you want to in ignoring specific text, but does alleviate the need to, so your code comparison just flags up the changes you want to focus on. Also means when you approve the changes the developer doesn't need to then amend all their bastardised/personalized object references.
Like i say, just the way I navigate round your issue. -
Actually I take that back. If you can synonym system views to look at a different db, that would my immediate task easier but wouldn't solve the general problem
-
yes you can create a synonym which points at a system object ; but obviously you need to give the synonym a different name so that it doesn't clash with the name of that system object on the referencing database.USE [tempdb]GOCREATE SYNONYM systables FOR sys.tablesgoSELECT TOP (1) * from systablesDROP SYNONYM systablesGOCREATE SYNONYM systables FOR master.sys.tablesgoSELECT TOP (1) * from systablesDROP SYNONYM systables
Add comment
Please sign in to leave a comment.
now, this all works fine, but in lower environments, devs have their own copies of these dbs, often appending their initials to differentiate. That's also OK *except* when comparing databases. How can I tell SQL Compare to compare MyGoodData_ab with MyGoodData_cd but ignore differences where the only difference is in the staging database names?
e.g. in MyGoodData_ab I might have a proc with a line like:
if exists (select * from MyStaging_ab.sys.tables where name = 'foobar')
and the proc is identical in MyGoodData_cd except for the same line where it reads;
if exists (select * from MyStaging_cd.sys.tables where name = 'foobar')
The only difference is the reference to a different database in the 3-part name. That's what I don't care about.
As it is, after SQL Compare runs, I have to manually filter the results to exclude examples like this. Any clever way to bend SQL Compare to my will and get it to exclude these "false positives"?