Click here to Skip to main content
15,920,438 members
Articles / Database Development
Tip/Trick

Easy Compare DB

Rate me:
Please Sign up or sign in to vote.
4.82/5 (12 votes)
24 Apr 2023CPOL2 min read 20.9K   859   38   14
Compare two SQL Server databases and show difference in tables and fields
This script connects to two SQL Server databases and compares them. This is especially useful when we have a development database and a production database, and we want to know the changes that must be made before sending to production.

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:

JSON
{
  "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.

C#
// 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.

C#
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

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Freelance
Spain Spain
Software Engineer from Barcelona (Spain) with years of experience. Passionate about programming.

Comments and Discussions

 
GeneralTables with the same name within different schemas Pin
semicoder7-May-23 4:03
semicoder7-May-23 4:03 
SuggestionCan Stored Procedures and Indexes also be added to the Diff Compare Pin
Divakar.Odessa4-May-23 0:47
Divakar.Odessa4-May-23 0:47 
SuggestionOther DB objects and actions Pin
Fly Gheorghe25-Apr-23 1:08
Fly Gheorghe25-Apr-23 1:08 
GeneralRe: Other DB objects and actions Pin
Divakar.Odessa3-May-23 23:55
Divakar.Odessa3-May-23 23:55 
GeneralRe: Other DB objects and actions Pin
Divakar.Odessa3-May-23 23:55
Divakar.Odessa3-May-23 23:55 
QuestionNice Pin
jochance1-Jul-22 9:50
jochance1-Jul-22 9:50 
GeneralFor moderators checking my previous message Pin
F Margueirat28-Jun-22 6:14
F Margueirat28-Jun-22 6:14 
I posted a message in Spanish, suggesting the author to avoid using special characters in the code. It was flagged as spam.
SuggestionRemover tildes del código Pin
F Margueirat28-Jun-22 8:03
F Margueirat28-Jun-22 8:03 
GeneralRe: Remover tildes del código Pin
Eligio Morgado H.29-Jun-22 20:41
professionalEligio Morgado H.29-Jun-22 20:41 
QuestionNice and useful tool Pin
Salam Y. ELIAS28-Jun-22 2:57
professionalSalam Y. ELIAS28-Jun-22 2:57 
AnswerRe: Nice and useful tool Pin
Eligio Morgado H.29-Jun-22 20:40
professionalEligio Morgado H.29-Jun-22 20:40 
GeneralRe: Nice and useful tool Pin
Salam Y. ELIAS29-Jun-22 22:22
professionalSalam Y. ELIAS29-Jun-22 22:22 
GeneralRe: Nice and useful tool Pin
Eligio Morgado H.29-Jun-22 23:10
professionalEligio Morgado H.29-Jun-22 23:10 
GeneralRe: Nice and useful tool Pin
Salam Y. ELIAS29-Jun-22 23:55
professionalSalam Y. ELIAS29-Jun-22 23:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.