You can compare two schemas to produce a migration script.

Here I have two versions of the Northwind database. Northwind is my "production" database. I've made many changes in another database called "Northwind-Dev".

I can use the CompareSchemas tool to find the differences in tables, columns, constraints and stored procedures.

//get the base schema
var baseReader = new DatabaseReader(@"Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=Northwind", "System.Data.SqlClient");
var baseSchema = baseReader.ReadAll();
//get the comparison schema
var devReader = new DatabaseReader(@"Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=Northwind-Dev", "System.Data.SqlClient");
var devSchema = devReader.ReadAll();
//compare
var comparison = new CompareSchemas(baseSchema, devSchema);
var script = comparison.Execute();


The script shows all the changes. I added a column to a table and a unique constraint on that new column:

ALTER TABLE [Zoo] ADD [City] NVARCHAR (20)  DEFAULT '' NOT NULL;
ALTER TABLE [Zoo] ADD CONSTRAINT [City_Unique] UNIQUE ([City]);

The migration script will convert the first (base) database into the second (compare) database.

To get the reverse migration, simply reverse the order:

//reverse migration
var comparison = new CompareSchemas(devSchema, baseSchema);
var script = comparison.Execute();

This generates drop statements for my constraint and column

ALTER TABLE [Zoo] DROP CONSTRAINT [City_Unique]
ALTER TABLE [Zoo] DROP COLUMN [City];

In some cases the DatabaseSchemaReader may not have enough information to build the SQL DDL, so it will just show a SQL comment.

Although the DatabaseSchemaReader can read many database platforms, it can only write SQL for certain types including SqlServer, Oracle, SqlServerCe 4, MySQL, SqLite, PostgreSql, DB2. It will try to infer the output type from the provider name ("System.Data.SqlClient" in the above code). If it doesn't find an output format, it uses SqlServer conventions.

Reading a large schema can be slow, so you can just read the part you are interested in. Here I just read the table "Zoo":

//get the base schema
var baseReader = new DatabaseReader(@"Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=Northwind", "System.Data.SqlClient");
//just read the Zoo table, columns and constraints
baseReader.Table("Zoo");
//get the schema (just contains Zoo)
var baseSchema = baseReader.DatabaseSchema;
//get the comparison schema
var devReader = new DatabaseReader(@"Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=Northwind-Dev", "System.Data.SqlClient");
devReader.Table("Zoo");
var devSchema = devReader.DatabaseSchema;
//compare
var comparison = new CompareSchemas(baseSchema, devSchema);
var script = comparison.Execute();

 

 

Last edited Jul 5, 2011 at 11:35 AM by Martinjw, version 1

Comments

No comments yet.