SQL Compare: How to filter out changes to views/functions/procs based on content

gbritton

One environment I support has separate databases for staging and etl'd data.  e.g. data is loaded into MyStaging from various sources, cleaned up then finally loaded in to MyGoodData for downstream use.  That means that for some operations, we cannot avoid 3-part naming.  e.g.  when querying metadata (like sys.tables) in MyStaging from a proc in MyGoodData.  

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"?

Tianjiao_Li

Hi @gbritton,

Thanks for your suggestion.

Can you please kindly cast your vote on this user voice post? 

gbritton

I'd like too, but I'm out of votes

Tianjiao_Li

Hi @gbritton

Just to let you know we are looking at allocating more votes to you or other options.

Tianjiao_Li

Hi @gbritton

You should be able to vote now. Please give it a try and let us know if there is any issue.

kalo

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

gbritton

@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.

kalo

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. 

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 

( 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)

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.

gbritton

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

kalo

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]

GO

CREATE SYNONYM systables FOR sys.tables

go

SELECT TOP (1) * from systables

DROP SYNONYM systables

GO

CREATE SYNONYM systables FOR master.sys.tables

go

SELECT TOP (1) * from systables

DROP SYNONYM systables