How To Compare 2 Tables In Different Databases

In the video, SQL Basics: Compare Tables In 2 Different Databases, we look at how to compare two tables using SQL. In the context of the video, I am using SQL Server and the object INFORMATION_SCHEMA is supported in many SQL languages relative to permissions and platform.

One common issue that arises sometimes is that we need to compare two tables in different databases. As a quick point here — we can use this code to compare two databases in the same database if we wanted provided that we either (1) remove the database reference or (2) ensure the database reference is the same. In other cases, we may want to compare two tables in two different databases. For an example, one situation where I see this arise is when developers use SELECT * INTO ThatDatabase.dbo.NewTable FROM ThisDatabase.dbo.ExistingTable and some of the schema doesn’t align or the schema for one of the tables changed over time (new columns, definition, etc).

Code

SELECT
 t1.ORDINAL_POSITION
 , t1.TABLE_CATALOG CopyTable_Database
 , t2.TABLE_CATALOG CopiedTable_Database
 , t1.COLUMN_NAME CopyColumn
 , t2.COLUMN_NAME CopiedColumn
FROM [CopyDatabase].INFORMATION_SCHEMA.COLUMNS t1
 LEFT JOIN [CopiedDatabse].INFORMATION_SCHEMA.COLUMNS t2 ON t1.TABLE_NAME = t2.TABLE_NAME
  AND t1.COLUMN_NAME = t2.COLUMN_NAME
WHERE t1.TABLE_NAME = 'OurTable'

Not all SQL languages may support the above SQL and depending on the PaaS or SaaS solution, INFORMATION_SCHEMA may not be an object that you can use (plus relative to your security context, you may not be able to access this object).

In the linked video, we can see the result that this query will produce (note that for this article, my columns are named different). In our example result here, we see that the DOB column in CopyColumn does not exist in CopiedColumn and the result returns a NULL. One point to make here is the assumption that we don’t need to join on the ORDINAL_POSITION as there are contexts where this alternative join would actually make more sense (if we expect that a table will be ordered the same as another table).

In the above SQL, we query the INFORMATION_SCHEMA in both the copy and copied databases (the brackets are where you would replace with the actual database name). We also are only reviewing the column name schema; if I wanted to look deeper into these tables, I could include the data types along with specific definitions (see the below code). For an example, I might have a situation where one of the tables allows NULLs while the other table does not and this may be an underlying cause of an issue.

SELECT
 t1.ORDINAL_POSITION
 , t1.TABLE_CATALOG CopyTable_Database
 , t2.TABLE_CATALOG CopiedTable_Database
 , t1.COLUMN_NAME Copy_ColumnName
 , t2.COLUMN_NAME Copied_ColumnName
 , t1.DATA_TYPE Copy_DataType
 , t2.DATA_TYPE Copied_DataType
 , t1.CHARACTER_MAXIMUM_LENGTH Copy_ColumnLength
 , t2.CHARACTER_MAXIMUM_LENGTH Copied_ColumnLength
FROM [CopyDatabase].INFORMATION_SCHEMA.COLUMNS t1
 LEFT JOIN [CopiedDatabase].INFORMATION_SCHEMA.COLUMNS t2 ON t1.TABLE_NAME = t2.TABLE_NAME
  AND t1.COLUMN_NAME = t2.COLUMN_NAME
WHERE t1.TABLE_NAME = 'OurTable'

In the above code, we compare the data type and the length of the data type. In our example, we see that one column differs in both the data type and the length (the image snips out the leading columns since they are redundant from the previous image). This highlights that we can dig further when we compare tables — we could compare collation, nullable values, defaults, etc when we use INFORMATION_SCHEMA .

Note: all images in the post are either created through actual code runs or from Pixabay. The written content of this post is copyright; all rights reserved. None of the written content in this post may be used in any artificial intelligence.