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.
