Compare SQL Server Databases

Problem

I need to programmatically compare SQL Server tables, columns, indexes and constraints between databases to determine what is the same, differences and additions. How can this be accomplished?

Solution

In the first part of this tip series, I presented how to save a database schema to a JSON file, and how to create a database using that JSON file.

Part 2 presents how to compare a database against the schema saved in Part 1, and shows all differences, if any.

Comparing database schema with the schema we provide as a template (or skeleton) is done in three steps:

Get the database schema
Read the database schema from the JSON file
Compare these JSON structures, find the differences if any and present the comparison result to the user

This comparison can be easily extended to compare other elements of the database, like views (views definitions), stored procedures, etc. I challenge the reader to extend this comparison and include the above mentioned elements as well.

I already presented getting the MS SQL database schema and read database schema from a JSON file in the first part, so I will not repeat it here.

Comparing database consists in four steps:

Compare tables
Compare columns
Compare indexes
Compare constraints

How to Compare Two Tables in SQL Server

As you remember from first tip in this series, we neither read table names from the database, nor save them in the JSON file. Table names are part of reading columns. We presented how we can read table names from the JSON file. We use a similar code to get table names from database schema.

You will see from the column comparison that we detect any extra table in the database. You will also see that we also detect any extra tables in the JSON file. So why do we have an extra function to compare tables? We only check if we have any extra tables in the JSON file, because these tables need to be added later on to the database if we decide to modify the database to be exactly as described by the JSON file. When we detect an extra table in the JSON file, we store the entire table definition, such that we will be able to create that table later on. By comparing columns, we are not able to get the entire table definition which might be missing from database.

Comparing tables can be done with a code similar to this:

foreach(var schema in _tablesFromJson)
{
  foreach(var tableName in schema.tables)
  {
    string schemaName = schema.schemaName;
    if(!FoundTable(schemaName, tableName, _tablesFromDb))
    {
      // table not found, get its columns and create it
      string fullTableName = "[" + schemaName + "].[" + tableName + "]";
      _diffmsg += "\r\nTable " + fullTableName + " is missing from database";

      //missingElementsFromDb[colnamejs] = tableNameJson;
      _haveDiff = true;

      foreach(var (schemaNameJson, tableJson) in
        from JToken tokenJson in _columnsFromJson
        let schemaNameJson = (string)tokenJson["Table Schema"]
        let tableJson = (JArray)tokenJson["Tables"]
        where schemaNameJson == schemaName //&& tableJson["Table Name"] == tableName
        select (schemaNameJson, tableJson))
      {
        foreach(var (table, tableNameJson) in
          from JToken tokenJsonTbl in tableJson
          let table = (JArray)tokenJsonTbl["Table Columns"]
          let tableNameJson = (string)tokenJsonTbl["Table Name"]
          where schemaNameJson == schemaName //&& tableJson["Table Name"] == tableName
          select (table, tableNameJson))
        {
          _extraTablesInJson[fullTableName] = table;
        }
      }
    }
  }
}

How to Compare Columns in SQL Server

Comparing columns in SQL Server is done basically in two steps: for each column in the JSON file we check if it exists in the database, and the other way around. We need to compare the whole column definition, because, for example, we need to expand a varchar column, and those columns will not be the same.

We will use the function called CompareColumns() which looks like:

foreach(JToken schemaDb in _columnsFromDb)
{
    // Table schema should NEVER be null! It always contains at least "dbo" as schema name
    string schemaNameDb = (string)schemaDb["Table Schema"];
    JArray tablesFromSchemaDB = (JArray)schemaDb["Tables"]; // Get the tables in each schema

    foreach(JToken schemaJson in _columnsFromJson)
    {
        // Table schema might be missing from Json file
        string schemaNameJson = (string)schemaJson["Table Schema"];
        if(schemaNameDb != schemaNameJson) // Different schema
            continue;
        JArray tablesFromSchemaJson = (JArray)schemaJson["Tables"];
        foreach(JToken tableDb in tablesFromSchemaDB)
        {
            string tableNameDb = (string)tableDb["Table Name"];
            string fullTableName = "[" + schemaNameDb + "].[" + tableNameDb + "]";

            if(!FoundTable(schemaNameDb, tableNameDb, _tablesFromJson))
            {
                _diffmsg += "\r\nFound extra table " + fullTableName + " in database";
                _haveDiff = true;
                continue;
            }

            JArray arraydb = (JArray)tableDb["Table Columns"];
            foreach(var (tableJson, tableNameJson) in
                from JToken tableJson in tablesFromSchemaJson
                let tableNameJson = (string)tableJson["Table Name"]
                select (tableJson, tableNameJson))
            {
                // We search if this table from db structure is in database, 
                // otherwise we need to create it
                string jsonTableName = "[" + schemaNameJson + "].[" + tableNameJson + "]";
                if(!FoundTable(schemaNameDb, tableNameDb, _tablesFromDb))
                {
                    _diffmsg += "\r\nMissing table " + jsonTableName + " from database";
                    _haveDiff = true;
                    continue;
                }
                if(tableNameDb != tableNameJson) // different table
                    continue;
                JArray arrayjs = (JArray)tableJson["Table Columns"];
                CompareColumnsFromTable(fullTableName, arraydb, arrayjs);
            }
        } // foreach(JToken tableDb in tablesFromSchemaDB)
    } // foreach(JToken schemaJson in _columnsFromJson)
} // foreach(JToken schemaDb in _columnsFromDb)

We have two functions in this code. First one searches if a table exists in the Json structure which contains all columns. This function looks like:

private bool FoundTable(string schemaName, string tableName, dynamic where)
{
    return ((IEnumerable)where).Cast<dynamic>()
        .Where(s => s.schemaName.ToString() == schemaName)
        .Select(t => t.tables).ToList().First().Contains(tableName);
}

The second function compares columns of the same table:

private void CompareColumnsFromTable(string tableName, JArray tableDb, JArray tableJso)
{
    JObject missingColumnsFromDb = new JObject();
    JObject missingColumnsFromJson = new JObject();
    JObject differentColumns = new JObject();

    // Compare each column of database with each column of schema
    foreach(var (coldb, colnamedb) in from JToken coldb in tableDb
                                      let colnamedb = (string)coldb["Column Name"]
                                      select (coldb, colnamedb))
    {
        bool found = false;
        foreach(var coljs in from JToken coljs in tableJson
                             let colnamejs = (string)coljs["Column Name"]
                             where colnamejs != null && colnamedb == colnamejs
                             select coljs)
        {
            found = true;
            if(!JToken.DeepEquals(coldb, coljs)) // we have differences
            {
                _diffmsg += "\r\nIn table " + tableName + " column [" +
                colnamedb + "] is different in database";

                // Nqw we store the full columns definition
                // We need this when deciding if column change is possible
                JObject differences = new JObject
                {
                    ["database"] = coldb,
                    ["json"] = coljs
                };
                _haveDiff = true;
                break;
            }
        }
        if(!found)
        {
            _diffmsg += "\r\nIn table " + tableName + " column [" +
                colnamedb + "] is missing from schema file";
            _haveDiff = true;
        }
    }

    // Compare each column of schema with each column of database
    foreach(var (coljs, colnamejs) in from JToken coljs in tableJson
                                      let colnamejs = (string)coljs["Column Name"]
                                      select (coljs, colnamejs))
    {
        bool found = false;

        // Here we look ONLY for columns in json file which are NOT in the database
        // These columns need to be added in the database
        foreach(var _ in from JToken coldb in tableDb
                         let colnamedb = (string)coldb["Column Name"]
                         where colnamejs == colnamedb
                         select new
                         {
                         })
        {
            found = true;
            break;
        }

        if(!found)
        {
            _diffmsg += "\r\nIn table " + tableName + " column [" +
                colnamejs + "] is missing from database";
            _haveDiff = true;
        }
    }
}

How to Compare Indexes in SQL Server

After we compare columns, we can compare SQL Server indexes. We do it in two steps.

In the first step for each element in the JSON file we should find the same element in the database structure (same name, same columns and order of columns in the index).

foreach(var (schemaNameJson, tableNameJson, fullJsonTableName, arrayjs) in
    from JToken tableJson in _indexesFromJson
    let schemaNameJson = (string)tableJson["Table Schema"]
    let tableNameJson = (string)tableJson["Table Name"]
    let fullJsonTableName = "[" + schemaNameJson + "].[" + tableNameJson + "]"
    let arrayjs = (JArray)tableJson["Indexes"]
    select (schemaNameJson, tableNameJson, fullJsonTableName, arrayjs))
{
    foreach(var (coljs, colnamejs) in
        from JToken coljs in arrayjs
        let colnamejs = (string)coljs["index_name"]
        select (coljs, colnamejs))
    {
        bool found = false;
        foreach(var (tableNameDb, fullDbTableName, arraydb) in
            from JToken tableDb in _indexesFromDb
            let schemaNameDb = (string)tableDb["Table Schema"]
            let tableNameDb = (string)tableDb["Table Name"]
            let fullDbTableName = "[" + schemaNameDb + "].[" + tableNameDb + "]"
            let arraydb = (JArray)tableDb["Indexes"]
            select (tableNameDb, fullDbTableName, arraydb))
        {
            // Compare each index from database with each index from schema
            foreach(var (coldb, colnamedb) in
                from JToken coldb in arraydb
                let colnamedb = (string)coldb["index_name"]
                where fullJsonTableName == fullDbTableName && colnamejs == colnamedb
                select (coldb, colnamedb))
            {
                if(!JToken.DeepEquals(coldb, coljs)) // we have differences
                {
                    _diffmsg += "\r\nIn table " + fullJsonTableName + " index [" +
                        colnamejs + "] is different in database";
                    _haveDiff = true;
                }
                found = true;
            }
            if(found) // exit this cycle as well, we found the same index
                break;
        }
        if(!found)
        {
            _diffmsg += "\r\nIndex [" + colnamejs + "] is missing from database, table " +
                fullJsonTableName;
            _extraIndexesInJson.Add
            (new JObject
            {
                ["Table Name"] = fullJsonTableName,
                ["Index"] = coljs.DeepClone()
            }
            );
            _haveDiff = true;
        }
    }
}

In the second step for each element in the database structure we should find the same element in the JSON file.

foreach(var (schemaNameDb, tableNameDb, fullDbTableName, arraydb) in
    from JToken tableDb in _indexesFromDb
    let schemaNameDb = (string)tableDb["Table Schema"]
    let tableNameDb = (string)tableDb["Table Name"]
    let fullDbTableName = "[" + schemaNameDb + "].[" + tableNameDb + "]"
    let arraydb = (JArray)tableDb["Indexes"]
    select (schemaNameDb, tableNameDb, fullDbTableName, arraydb))
{
    foreach(var (coldb, colnamedb) in
        from JToken coldb in arraydb
        let colnamedb = (string)coldb["index_name"]
        select (coldb, colnamedb))
    {
        bool found = false;
        foreach(var (schemaNameJson, tableNameJson, fullJsonTableName, arrayjs) in
            from JToken tableJson in _indexesFromJson
            let schemaNameJson = (string)tableJson["Table Schema"]
            let tableNameJson = (string)tableJson["Table Name"]
            let fullJsonTableName = "[" + schemaNameJson + "].[" + tableNameJson + "]"
            let arrayjs = (JArray)tableJson["Indexes"]
            select (schemaNameJson, tableNameJson, fullJsonTableName, arrayjs))
        {
            // Compare each index from database with each index from schema
            foreach(var colnamejs in
                from JToken coljs in arrayjs
                let colnamejs = (string)coljs["index_name"]
                where fullJsonTableName == fullDbTableName && colnamejs == colnamedb
                select colnamejs)
            {
                found = true;
                break;
            }
            if(found)
                break;
        }
        if(!found)
        {
            _diffmsg += "\r\nIndex [" + colnamedb + "] is missing from schema file, table " +
                fullDbTableName;
            _haveDiff = true;
        }
    }
}

Comparing foreign keys and other constraints is done in a very similar way to comparing indexes. I let the reader to write this code.

Next Steps

Part III will change database, without losing data, such that the database will have the same structure as the one we provide to compare. There is full support for always encrypted columns. This means if you want to decrypt an always encrypted column you can do it, as well as if you want to transform a normal column into an always encrypted, you can also do it. This will be done without using PowerShell SSMS.
Part III will have attached the full project and some JSON database structures to play with.

About the author

Mircea Dragan is a Senior Computer Scientist with a strong mathematical background with over 18 years of hands-on experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2020-06-29

Compare SQL Server Databases - Part 2