cjw cjw - 7 months ago 13
SQL Question

Find column differences between tables with the same name in two different databases

I have two databases, and both of them have the exact same table names. However, certain columns between the matching tables have been added and removed between the two databases. I am trying to figure out how to write a query that can return the columns only in the first database and columns only in the second database.

I have been using INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS, but I am stuck since I can't find a way to compare the INFORMATION_SCHEMA between two databases. I was hoping this syntax would work to differentiate the two databases, but it does not:

[Database1]..[INFORMATION_SCHEMA].[COLUMNS]


If you can tell by now, I am not too experienced with SQL

Any help is appreciated!

Answer

As its name suggests, INFORMATION_SCHEMA is a schema. Hence, the three part naming is:

from Database1.INFORMATION_SCHEMA.COLUMNS

You have two periods together, which suggests the default schema, but that is not where the views are.

You can use four-part naming if you want to access the views on another server.