Script to compare tables between two databases

Question

Script to compare tables between two databases

jdc

Old Hand

Points: 367
More actions
August 2, 2019 at 9:44 pm

#3668406

Hi,
I have same set of tables in Database A and Database B. If there any changes occurs to a table in Database A, then I have to make same changes in database B.
Is there way to query and find the differences between tables in Database A & Database B?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

You could use SSDT to run a schema compare. This can be an automated process, if required.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jdc Old Hand Points: 367 More actions · Answer 2

I have a query which shows the differences but it is not giving if the column datatype changes. How do I get that?

select isnull(db1.table_name, db2.table_name) as 
,
isnull(db1.column_name, db2.column_name) as [column],
db1.column_name as DB1,
db2.column_name as DB2
from
(select schema_name(tab.schema_id) + '.' + tab.name as table_name,
col.name as column_name
from database1.sys.tables as tab
inner join database1.sys.columns as col
on tab.object_id = col.object_id) db1
full outer join
(select schema_name(tab.schema_id) + '.' + tab.name as table_name,
col.name as column_name
from database2.sys.tables as tab
inner join database2.sys.columns as col
on tab.object_id = col.object_id) db2
on db1.table_name = db2.table_name
and db1.column_name = db2.column_name
where (db1.column_name is null or db2.column_name is null)
order by 1, 2, 3

This reply was modified 5 years, 3 months ago by jdc.

Joe Torre SSChampion Points: 10248 More actions · Answer 3

You can find new columns and tables using a left join with where the column name or table name is null. Then you have to decide what to do if the column or table is dropped. Do you want to drop it from the target or just ignore it when comparing the data. Then you can delete the data no longer in the source, update where it is changed if you have a way of knowing that, like an updatedDate column, then inserting new data not in the target. In addition you could make the tables in the target database temporal tables so you can query the state of the data at a given time.