Easy Compare DB

• Download source - 9.5 KB

Introduction

The intention of this article is to show a simple script in .NET 6 that compares two databases to find differences in their structure.

The need appeared when I had to replicate some changes made in the development database to production. I needed an effortless way to list the differences in the schema of both databases.

This script displays in console:

• Tables in database 2 missing in database 1
• Tables in database 1 missing in database 2
• Fields on each table of database 2 missing in database 1
• Fields on each table of database 1 missing in database 2
• Difference in fields: schema, data type, length, precision, scale, null and identity
• Views in database 1 missing in database 2
• Views in database 2 missing in database 1
• Differences in views code

Using the Code

Configuration

In the first part, we need to modify the configuration file. We have two sections to configure:

{
  "ConnectionStrings": {
    "db1_connectionstring": "Server=SRV1;Database=DB1;User ID=Usr1;
     Password=Pwd1;Trusted_Connection=False;Encrypt=True;TrustServerCertificate=True",
    "db2_connectionstring": "Server=SRV2;Database=DB2;User ID=Usr2;
     Password=Pwd2;Trusted_Connection=False;Encrypt=True;TrustServerCertificate=True"
  },
  "DbNames": {
    "db1_name": "db1",
    "db2_name": "db2"
  },
  "Comparing": {
    "schema": true,
    "dataType": true,
    "length": true,
    "precision": true,
    "scale": true,
    "nullable": true,
    "identity": true
  }
}

Connection strings

You only must change both connection strings. This is the only required change you must do.

Comparing

Here, you can set which attributes you want to compare. By default, it compares everything.

Comparing Schemas

The next part of the script performs the comparison of the two schemas.

This comparison is made by parts:

• First, tables that do not exist in both databases are searched for.
• Then, for all those tables that are in both databases, they are analyzed field by field.
  • First, we look for fields that do not exist in both tables.
  • Matching fields are then parsed depending on the configuration, to compare their schema, data type, precision, etc.

The script is based on this function that returns a Field object with the information of all the fields of the database.

// Get list of fields object from a database
IEnumerable<Field> FillFields(string connectionString)
{
    using var connection = new SqlConnection(connectionString);

    connection.Open();

    var reader = new SqlCommand(@"SELECT schema_name(tab.schema_id) 
                 as schema_name, tab.name as table_name, 
                 col.name as column_name, t.name as data_type,
        col.max_length, col.precision, col.scale, col.is_nullable, col.is_identity
        FROM sys.tables as tab INNER JOIN 
            sys.columns as col ON tab.object_id = col.object_id LEFT JOIN 
            sys.types as t ON col.user_type_id = t.user_type_id
        ORDER BY schema_name, table_name, col.name", connection).ExecuteReader();

    while (reader.Read()) yield return new Field
    {
        Schema = reader["schema_name"].ToString(),
        Table = reader["table_name"].ToString(),
        Column = reader["column_name"].ToString(),
        DataType = reader["data_type"].ToString(),
        Length = short.Parse(reader["max_length"].ToString()),
        Precision = byte.Parse(reader["precision"].ToString()),
        Scale = byte.Parse(reader["scale"].ToString()),
        Nullable = bool.Parse(reader["is_nullable"].ToString()),
        Identity = bool.Parse(reader["is_identity"].ToString()),
    };
}

Then, using Linq, the rest of the comparisons are done.

Output

Because I only needed to know those differences, I write the output to the console screen. However, it would be quite easy to modify the script to serialize the information to a text file, for example.

The Source Code

To keep it as simple as possible, I used a .NET 6 console project with C#, without using the static void entry point.

The application uses some lambda functions.

IEnumerable<string> MissingTables(Field[] comp1, Field[] comp2) => comp1.Where
(x => !comp2.Select(x => x.Table).Distinct().Contains(x.Table)).OrderBy
(x => x.Table).Select(x => x.Table).Distinct();

I use also a Nuget package Microsoft.Data.SqlClient and both packages for managing config file.

History

• 27^th June, 2022: Initial version
• 23^th April, 2023: Added view comparing