Comparing to ALTER scripts instead of CREATE

tobin

Hey guys,

All of our scripts in source control follow a pattern like this:

<div>IF ISNULL(OBJECT_ID('dbo.vwView'),0) = 0 EXEC ('CREATE VIEW dbo.vwView AS SELECT 1 fldDummy')</div><div>GO</div><div>SET QUOTED_IDENTIFIER ON</div><div>SET ANSI_NULLS ON</div><div>GO</div><div>ALTER VIEW [dbo].[vwView]</div><div><span>AS</span></div>

Unfortunately, SQL Compare is saying these scripts aren't identical/mapped, because it seems to only want to compare scripts listed as CREATE and not ALTER.

We use ALTER specifically so we don't have problems with permissions, code signing and service brokers, as well as not interrupting production systems when we deploy.

Is there any options or ways to get this working to compare the ALTER scripts to our database?

Thanks,

Andrew

sam.blackburn

Scripts folders in SQL Compare are intended to be a readable, diff-friendly representation of the objects in your database, and don't support arbitrary SQL.  In your example, it would be difficult for SQL Compare to detect what the conditional EXEC is doing to the schema of the database.

That said, SQL Compare can register the SQL Server 2016 CREATE OR ALTER VIEW syntax from a scripts folder, which looks like it might satisfy your use case.

Does this help?

tobin

Hey Sam,

Unfortunately not, as we're stuck to SQL 2014 for the time being.

I've commented out the conditional EXEC, and even removed it, but the script still won't compare unless the query is marked as CREATE and not ALTER, so I can't get the guys to comment out the create/replace part and then compare just the script while it is an ALTER VIEW.

Any other suggestions?

Matthew_Chappelow

We did look at changing the way SQL Compare interprets the scripts folder to allow for this case but decided not to proceed.

It might be worth investigating whether SQL Change Automation might help with your case; otherwise I can't think of another workaround for your situation here.