Microsoft SQL Server : finding columns differences between two separate databases

Question

I am looking to have a small test that I can run to compare two separate databases that could be almost identical in table structures.

Here is what I'm looking at...

MSSQLSERVER (Instance)
|  
|-- MY_DB1 
    |
    |-- Table_A 
        |-- Column_Foo
        |-- Column_Boo
|-- MY_DB2
    |
    |-- Table_A
        |-- Column_Foo
        |-- Column_WTF
        |-- Column_Boo

Excuse the crude diagram above, this was just to illustrate the idea. As you can see we have a SQL Server instance MSSQLSERVER and it has two separate restored databases. Both of these databases have multiple database tables. I need this test to compare both MY_DB1 and MY_DB2 databases and show me a list of any tables that have different columns.

Based on above example, the output would indicate:

MY_DB2 
|
|-- Table_A 
    |-- Column_WTF

It would show Column_WTF because this was not found in the MY_DB1.

I always need to know if the columns are a 100% match, if they are not then it should provide output that would indicate where the differences are.

I would like to do this with T-SQL, but I'm also open to any opensource tools that may already have this functionality for simple comparison.

Thanks for any help.

Do you have Visual Studio? It comes with some strong diff tools. — tgolisch, May 29 '18 at 15:14
To do this with sql you need to look at sys.tables and sys.columns. You would also want to dig into the datatypes and make sure they are the same. But honestly doing a compare yourself gets really deep really fast once you start getting into defaults, constraints, foreign keys etc. There are plenty of third party tools that do this quite nicely. — Sean Lange, May 29 '18 at 15:14
Any recommendations on free opensource tools to do heavy-lifting? — jremi, May 29 '18 at 15:21
Here's a thread to get you started: https://stackoverflow.com/questions/193438/what-is-a-free-tool-to-compare-two-sql-server-databases — MJH, May 29 '18 at 15:26

score 2 · Accepted Answer · answered May 29 '18 at 15:18

2

Perhaps something like this

Example

;with cte as (
Select [Table_Catalog]
      ,[Table_Schema]
      ,[Table_Name]
      ,[Column_Name]
      ,Cnt = sum(1) over (Partition By [Table_Name],[Column_Name])
 From (
        Select * From [My_DB1].INFORMATION_SCHEMA.COLUMNS 
        Union All
        Select * From [My_DB2].INFORMATION_SCHEMA.COLUMNS 
       ) A
) 
Select * From cte Where Cnt=1

answered May 29 '18 at 15:18

John Cappelletti

79,615
7
44
66

1

John this is awesome, it might be exactly what i need. I just ran the query and it gave me some super valuable results. Much appreciated! – jremi May 29 '18 at 15:22
1

@jremi Always happy to help – John Cappelletti May 29 '18 at 15:23

score 0 · Answer 2 · answered May 29 '18 at 15:26

Imo, the simplest approach is to generate the DDL for the two databases and use difftool (from Visual Studio) to compare the DDL.

In SSMS, right-click your DB (MY_DB1), "Script database as", "Create to", File. Repeat for MY_DB2.

From the (visual studio) "Developers command prompt": devenv.exe /diff file1 file2

The file arguments must have the full path to the files.

Microsoft SQL Server : finding columns differences between two separate databases

2 Answers2