1
Vote

CompareSchemas.Execute() ignores indexes

description

The resulting diff sql doesn't contain index creation queries.

comments

Martinjw wrote Nov 20, 2015 at 7:05 PM

When I try the following, it works. Can you give me more information?

Database 1 (SqlServer)
CREATE TABLE [dbo].[Products](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Name2] [nvarchar](50) NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Products] ON [dbo].[Products] ([Name] ASC)
GO
Database 2 is the same with this addition (an additional index):
CREATE NONCLUSTERED INDEX [IX_Products_Name2] ON [dbo].[Products] ([Name2] ASC)
GO
So the comparison diff should include a CREATE INDEX. And it does, with this code.
const string providername = "System.Data.SqlClient";
var connectionString = @"Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=Test";
var connectionString2 = @"Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=Test2";

var schema1 = new DatabaseReader(connectionString, providername){ Owner = "dbo" }.ReadAll();
var schema2 = new DatabaseReader(connectionString2, providername) { Owner = "dbo" }.ReadAll();

var comparison = new CompareSchemas(schema1, schema2);
var script = comparison.Execute();

//script is CREATE INDEX [IX_Products_Name2] ON [dbo].[Products]([Name2]);

inqb wrote Nov 25, 2015 at 12:25 PM

It only works when comparing existing tables.
CompareTables.Execute() ignores indexes in newTables.
You can reproduce it by comparing schema containing a table with an index with an empty schema.