CODEWITHSUNDEEP
sqlsql-server
Daedalus
Daedalus

Reputation: 4272

Get column differences between 2 databases (SQL Server)

I've got 2 databases almost identical to one another. However, it seems that for some tables in the new database, they are missing columns that are in the old database.

What would be the best way to see the differences between columns in tables between 2 databases? Specifically, I need to see what columns AREN'T in the new database that ARE in the old one.

I've tried looking this up but most things I found were either not what I needed or looking at "records".

Upvotes: 0

Views: 453

Answers (4)

dfundako
dfundako

Reputation: 8324

You can query the columns from your db using the sys tables and compare the result sets. This script assumes your old db has all the columns you want. 

;WITH old_db_columns AS (
SELECT c.object_id, c.column_id, c.name AS column_name, t.name AS table_name
FROM old_db.sys.tables t 
INNER JOIN old_db.sys.columns c 
    ON t.object_id = c.object_id
)
, new_db_columns AS (
    SELECT c.object_id, c.column_id, c.name AS column_name, t.name AS table_name
    FROM new_db.sys.tables t 
    INNER JOIN new_db.sys.columns c 
        ON t.object_id = c.object_id
        )

SELECT * 
FROM old_db_columns o
WHERE NOT EXISTS (
SELECT 1
FROM new_db_columns n
WHERE n.table_name = o.table_name
AND n.column_name= o.column_name)

Upvotes: 2

KarlR
KarlR

Reputation: 1615

Check out that video: VS Comparision

Visual Studio has built in functionality that you are able to do data compares, schema compares and it will generate the differences for you in a script if you need to recitfy the variances.

Upvotes: 0

Pelin
Pelin

Reputation: 966

You can generate the create statement of the tables and you can compare them with using any diff tool.

Upvotes: 0

Adnand
Adnand

Reputation: 572

You may use SQL Compare and SQL Data Compare, tools by Red Gate, to compare and sync databases schema and data.

Upvotes: 1

Related Questions