SQL Compare 10.4 (and 10.5 beta) seems to create an invalid changescript in the situation that can be reproduced by the steps below.
To summarize: in the original database there's a table with an indexed view. In the target database there are both changes in the table definition and in the view definition and SQLCompare doesn't seem to handle this situation correctly when using DropAndCreateInsteadofAlter.
---- Simple test database
create database TestSQLCompareBug
go
use TestSQLCompareBug
go
create table TestTabel (number int, code char(3))
go
create view TestView
with schemabinding
as
select number, code
from dbo.TestTabel where number < 10
go
create unique clustered index TestIndex on TestView (number) with (STATISTICS_NORECOMPUTE=ON)
go
---- Make some changes in another version of the database
create database TestSQLCompareBug_WithChanges
go
use TestSQLCompareBug_WithChanges
go
-- Make a change to the table definition: add a column
create table TestTabel (number int, code char(3), description varchar(100))
go
create view TestView
with schemabinding
as
select number, code
from dbo.TestTabel where number < 10
go
-- Also make a change to the view definition
create unique clustered index TestIndex on TestView (number)
go
Now compare the two databases using:
SQLCompare.exe /s1:dbserver /s2:dbserver /db2:TestSQLCompareBug /db1:TestSQLCompareBug_WithChanges /sf:testsqlcomparebugchangescript.sql /force /options np,ie,ip,iq,iw,if,icm,incd,oec,dacia /include:ddltrigger /include:table /include:storedprocedure /include:view
In the resulting changescript the view is dropped and later recreated, but inbetween the script tries to recreate the index on the - by then dropped - view.
/*
Run this script on:
dbserver.TestSQLCompareBug - This database will be modified
to synchronize it with:
dbserver.TestSQLCompareBug_WithChanges
You are recommended to back up your database before running this script
Script created by SQL Compare version 10.4.8 from Red Gate Software Ltd at 24-03-2014 14:35:18
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
PRINT N'Removing schema binding from [dbo].[TestView]'
GO
IF OBJECT_ID(N'[dbo].[TestView]', 'V') IS NOT NULL
EXEC sp_executesql N'
ALTER view [dbo].[TestView]
as
select number, code
from dbo.TestTabel where number < 10
'
GO
PRINT N'Dropping [dbo].[TestView]'
GO
IF OBJECT_ID(N'[dbo].[TestView]', 'V') IS NOT NULL
DROP VIEW [dbo].[TestView]
GO
PRINT N'Altering [dbo].[TestTabel]'
GO
IF COL_LENGTH(N'[dbo].[TestTabel]', N'description') IS NULL
ALTER TABLE [dbo].[TestTabel] ADD[description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
PRINT N'Altering [dbo].[TestView]'
GO
IF OBJECT_ID(N'[dbo].[TestView]', 'V') IS NOT NULL
EXEC sp_executesql N'
ALTER view [dbo].[TestView]
with schemabinding
as
select number, code
from dbo.TestTabel where number < 10
'
GO
PRINT N'Creating index [TestIndex] on [dbo].[TestView]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'TestIndex' AND object_id = OBJECT_ID(N'[dbo].[TestView]'))
CREATE UNIQUE CLUSTERED INDEX [TestIndex] ON [dbo].[TestView] ([number]) ON [PRIMARY]
GO
PRINT N'Creating [dbo].[TestView]'
GO
IF OBJECT_ID(N'[dbo].[TestView]', 'V') IS NULL
EXEC sp_executesql N'
create view [dbo].[TestView]
with schemabinding
as
select number, code
from dbo.TestTabel where number < 10
'
GO
Output from running this script:
Removing schema binding from [dbo].[TestView]
Dropping [dbo].[TestView]
Altering [dbo].[TestTabel]
Altering [dbo].[TestView]
Creating index [TestIndex] on [dbo].[TestView]
Msg 1088, Level 16, State 12, Line 2
Cannot find the object "dbo.TestView" because it does not exist or you do not have permissions.
Creating [dbo].[TestView]
To summarize: in the original database there's a table with an indexed view. In the target database there are both changes in the table definition and in the view definition and SQLCompare doesn't seem to handle this situation correctly when using DropAndCreateInsteadofAlter.
Now compare the two databases using:
In the resulting changescript the view is dropped and later recreated, but inbetween the script tries to recreate the index on the - by then dropped - view.
Output from running this script: