How to Compare Data Between Two Table in Different Databases Using SQL Server 2008

SQL Server 2008 Compare two different database tables for column name, data type and length

Resolved it my self :

SELECT  
    T.[name] AS [table_name], AC.[name] AS [column_name],  
        TY.[name] AS system_data_type
FROM    [master].sys.[tables] AS T  
    INNER JOIN [master].sys.[all_columns] AC ON T.[object_id] = AC.[object_id] 
    INNER JOIN [master].sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]

EXCEPT

SELECT  
    T.[name] AS [table_name], AC.[name] AS [column_name],  
        TY.[name] AS system_data_type
FROM    test.sys.[tables] AS T  
    INNER JOIN test.sys.[all_columns] AC ON T.[object_id] = AC.[object_id] 
    INNER JOIN test.sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]

This will give you the list of Columns along with Table Name, Column Name and Data Type that is in master but not exists or different from test.

How do I compare two tables in different databases

Yes, this can be done.

You could use something like a three part identifier to identify the tables in different database. Kinda like this :

RICSTOREV341.dbo.Price_Tab

Then you can perform a join on the primary key and fetch the result.

two different databases (data were copied from one 2 another) and compare their data

SELECT * FROM old.A WHERE old.A.name NOT IN (SELECT Name FROM new.B)

This will bring up all records from the original table which haven't been copied over to the new table. Bear in mind that this is MySQL syntax, so you may need to adapt it for SQL Server 2008.

Obviously this assumes that name is a unique column. If it isn't, you may need to post more specific detail on your particular problem.

Compare Two Tables In two Databases with the same Table Structure

I think you want not exists:

update a2
set min_qty = 0, max_qty = 0, unit_cost = 0 
from database2.asset_table a2
where not exists (
    select 1 from database1.asset_table a1 where a1.asset_id = a2.asset_id
)

Comparing values of 2 tables and listing the rows that are different

This will give you everything in A that's not in B

select * from tableA
Except
select * from tableB

and vice-versa

select * from tableB
Except
select * from tableA

Edit: Joined this way:

(select * from tableA
Except
select * from tableB)
union all
(select * from tableB
Except
select * from tableA)

---

Related Topics