How to compare database schema with baseline

It is easy to create a comparison project to compare the schema of tables in a database and a saved baseline stored in Excel. After all, the database schema is stored in system views and contains full information about your columns, data types, stored procedures, and other elements of a typical database.

For this post, I limited the schema information to the list of columns returned by the query 

select * from information_schema.columns

Note: This post uses SQL Server databases AdventureWorks2014 and AdventureWorks2008 databases as example environments, but you can connect any other database technology - MySQL, Oracle, MariaDB, PostgreSQL, and more!

Create your data baseline

I selected the information about columns defined in AdventureWorks2008 and exported it to an Excel workbook using SelectCompare.

You can save a baseline when you are creating the data connection for a comparison project. Enter your query, run it against SQL Server, and export it to Excel by clicking the export icon at the bottom of the screen. Next, you have to create a connection to Excel to be able to select data from the baseline. The process is described in this post on our website: https://selectcompare.com/blogs/news/create-excel-baseline-for-your-data

Modify your database - or let developers do it!

When you have the baseline, you can modify your database. In this case, I ran a few SQL statements in the database AdventureWorks2014 to modify columns, rename a table and create a new table. 

Compare database schema with the baseline

A comparison project consists of two data connections, their corresponding queries, and the list of columns returned by the queries. 

For the database connection, you have to write the same query that was used to create the baseline: 

For the Excel connection, the syntax is different. The data from the information_schema.columns table were exported to the spreadsheet and you have to select from the spreadsheet:

Note - you can find more about querying Excel spreadsheets on this page.

Create comparison key

An important step to perform when creating the project is to define the comparison key. You have to select one or more columns that uniquely identify rows on both sides of the comparison. If the key is not unique, your comparison results will be inaccurate! 

In our case, the key was created from the combination of attributes (table_schema, table_name, ordinal_position). Using these attributes to join the row set to the baseline allows for accurate identification of changes made to other attributes of the row sets, as well as the structure of the tables they belong to.

Choose only attributes that matter

When you write your queries for comparison, you may need to include attributes that will help you to analyze the data, but you don't want to include them in the comparison, because their values will be specific to a particular data source. For example, date_created is a useful audit attribute, but its value is likely to be different between the databases you compare. You can click the checkbox under the IGNORE_COLUMN header on the right-hand side of the project editor to let SelectCompare know that you don't want to use this attribute in the data comparison.

This video shows how to create a comparison project - in three minutes you will know if your database is in the state you think it is.