How to compare data between two table in different databases using Sql Server 2008?

I have two database's, named DB1 and DB2 in Sql server 2008. These two database's have the same tables and same table data also. However, I want to check if there are any differences between the data in these tables.

Coul开发者_开发百科d anyone help me with a script for this?

---

select * 
from (
      select 'T1' T, *
      from DB1.dbo.Table
      except
      select 'T2' T, *
      from DB2.dbo.Table
     ) as T
union all
select * 
from (
      select 'T2' T, *
      from DB2.dbo.Table
      except
      select 'T1' T, *
      from DB1.dbo.Table
     ) as T
ORDER BY 2,3,4, ..., 1  -- make T1 and T2 to be close in output 2,3,4 are UNIQUE KEY SEGMENTS

Test code:

declare @T1 table (ID int)
declare @T2 table (ID int)

insert into @T1 values(1),(2)
insert into @T2 values(2),(3)

select * 
from (
      select *
      from @T1
      except
      select *
      from @T2
     ) as T
union all
select * 
from (
      select *
      from @T2
      except
      select *
      from @T1
     ) as T

Result:

ID
-----------
1
3

Note: It can take long time to compare big table, when developing "tuned" solution or refactorig, which will give same result as REFERERCE - it may be wise to chekc simple parameters first: like

select count(t.*) from (
   select count(*) c0, SUM(BINARY_CHECKSUM(*)%1000000) c1 FROM T_REF_TABLE 
   -- select 12345 c0, -214365454 c1 -- constant values FROM T_REF_TABLE 
   except 
   select count(*) , SUM(BINARY_CHECKSUM(*)%1000000) FROM T_WORK_COPY 
) t

When this is empty, you have probably things under controll, and may be you can modify when you fail you will see "constant values FROM T_REF" to isert to save even more time for next check!!!

---

I’d really suggest that people who encounter this problem go and find a third party database comparison tool.

Reason – these tools save a lot of time and make the process less error prone.

I’ve used comparison tools from ApexSQL (Diff and Data Diff) but you can’t go wrong with other tools marc_s and Marina Nastenko already pointed out.

If you’re absolutely sure that you are only going to compare tables once then SQL is fine but if you’re going to need this from time to time you’ll be better off with some 3rd party tool.

If you don’t have budget to buy it then just use it in trial mode to get the job done.

I hope new readers will find this useful even though it’s a late answer…

---

I'v done things like this using the Checksum(*) function

In essance it creates a row level checksum on all the columns data, you could then compare the checksum of each row for each table to each other, use a left join, to find rows that are different.

Hope that made sense...

Better with an example....

select *
from 
( select checksum(*) as chk, userid as k from UserAccounts) as t1
left join 
( select checksum(*) as chk, userid as k from UserAccounts) as t2 on t1.k = t2.k
where t1.chk <> t2.chk 

---

select * from DB1.dbo.Table a inner join DB2.dbo.Table b on b.PrimKey = a.PrimKey 
where a.FirstColumn <> b.FirstColumn ...

Checksum that Matt recommended is probably a better approach to compare columns rather than comparing each column

---

Comparing the two Databases in SQL Database. Try this Query it may help.

SELECT T.[name] AS [table_name], AC.[name] AS [column_name],  TY.[name] AS 
   system_data_type FROM    [***Database Name 1***].sys.[tables] AS T  
   INNER JOIN [***Database Name 1***].sys.[all_columns] AC ON T.[object_id] = AC.[object_id]      
   INNER JOIN [***Database Name 1***].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    ***Database Name 2***.sys.[tables] AS T  
   INNER JOIN ***Database Name 2***.sys.[all_columns] AC ON T.[object_id] = AC.[object_id]  
   INNER JOIN ***Database Name 2***.sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]

---

If the database are in the same server use [DatabaseName].[Owner].[TableName] format when accessing a table that resides in a different database.

Eg: [DB1].[dbo].[TableName]

If databases in different server look at on Creating Linked Servers (SQL Server Database Engine)

---

Another solution (non T-SQL): you can use tablediff utility. For example if you want to compare two tables (Localitate) from two different servers (ROBUH01 & ROBUH02) you can use this shell command:

C:\Program Files\Microsoft SQL Server\100\COM>tablediff -sourceserver ROBUH01 -s
ourcedatabase SIM01 -sourceschema dbo -sourcetable Localitate -destinationserver
 ROBUH02 -destinationschema dbo -destinationdatabase SIM02 -destinationtable Lo
calitate

Results:

Microsoft (R) SQL Server Replication Diff Tool Copyright (c) 2008 Microsoft Corporation User-specified agent parameter values: 
-sourceserver ROBUH01 
-sourcedatabase SIM01 
-sourceschema dbo 
-sourcetable Localitate 
-destinationserver ROBUH02 
-destinationschema dbo 
-destinationdatabase SIM02 
-destinationtable Localitate 

Table [SIM01].[dbo].[Localitate] on ROBUH01 and Table [SIM02].[dbo].[Localitate ] on ROBUH02 have 10 differences. 

Err Id Dest. 
Only 21433 Dest. 
Only 21434 Dest. 
Only 21435 Dest. 
Only 21436 Dest. 
Only 21437 Dest. 
Only 21438 Dest. 
Only 21439 Dest. 
Only 21441 Dest. 
Only 21442 Dest. 
Only 21443 
The requested operation took 9,9472657 seconds.
------------------------------------------------------------------------

---

If both database on same server. You can check similar tables by using following query :

select 
      fdb.name, sdb.name 
from 
      FIRSTDBNAME.sys.tables fdb 
      join SECONDDBNAME.sys.tables sdb
      on fdb.name = sdb.name -- compare same name tables
order by 
      1     

By listing out similar table you can compare columns schema using sys.columns view.

Hope this helps you.

---

In order to compare two databases, I've written the procedures bellow. If you want to compare two tables you can use procedure 'CompareTables'. Example :

EXEC master.dbo.CompareTables 'DB1', 'dbo', 'table1', 'DB2', 'dbo', 'table2'

If you want to compare two databases, use the procedure 'CompareDatabases'. Example :

EXEC master.dbo.CompareDatabases 'DB1', 'DB2'

Note : - I tried to make the procedures secure, but anyway, those procedures are only for testing and debugging. - If you want a complete solution for comparison use third party like (Visual Studio, ...)

USE [master]
GO

create proc [dbo].[CompareDatabases]
    @FirstDatabaseName nvarchar(50),
    @SecondDatabaseName nvarchar(50)
    as
begin
    -- Check that databases exist
    if not exists(SELECT name FROM sys.databases WHERE name=@FirstDatabaseName)
        return 0
    if not exists(SELECT name FROM sys.databases WHERE name=@SecondDatabaseName)
        return 0

    declare @result table (TABLE_NAME nvarchar(256))
    SET NOCOUNT ON
    insert into @result EXEC('(Select distinct TABLE_NAME from ' + @FirstDatabaseName  + '.INFORMATION_SCHEMA.COLUMNS '
                                    +'Where TABLE_SCHEMA=''dbo'')'
                            + 'intersect'
                            + '(Select distinct TABLE_NAME from ' + @SecondDatabaseName  + '.INFORMATION_SCHEMA.COLUMNS '
                                    +'Where TABLE_SCHEMA=''dbo'')')

    DECLARE @TABLE_NAME nvarchar(256)
    DECLARE curseur CURSOR FOR
        SELECT TABLE_NAME FROM @result
    OPEN curseur
    FETCH curseur INTO @TABLE_NAME
        WHILE @@FETCH_STATUS = 0
            BEGIN
                print 'TABLE : ' + @TABLE_NAME
                EXEC master.dbo.CompareTables @FirstDatabaseName, 'dbo', @TABLE_NAME, @SecondDatabaseName, 'dbo', @TABLE_NAME
                FETCH curseur INTO @TABLE_NAME
            END
        CLOSE curseur
    DEALLOCATE curseur
    SET NOCOUNT OFF
end
GO

.

USE [master]
GO

CREATE PROC [dbo].[CompareTables]
    @FirstTABLE_CATALOG nvarchar(256),
    @FirstTABLE_SCHEMA nvarchar(256),
    @FirstTABLE_NAME nvarchar(256),
    @SecondTABLE_CATALOG nvarchar(256),
    @SecondTABLE_SCHEMA nvarchar(256),
    @SecondTABLE_NAME nvarchar(256)
    AS
BEGIN
    -- Verify if first table exist
    DECLARE @table1 nvarchar(256) = @FirstTABLE_CATALOG + '.' + @FirstTABLE_SCHEMA + '.' + @FirstTABLE_NAME
    DECLARE @return_status int
    EXEC @return_status = master.dbo.TableExist @FirstTABLE_CATALOG, @FirstTABLE_SCHEMA, @FirstTABLE_NAME
    IF @return_status = 0
        BEGIN
            PRINT @table1 + ' : Table Not FOUND'
            RETURN 0
        END



    -- Verify if second table exist
    DECLARE @table2 nvarchar(256) = @SecondTABLE_CATALOG + '.' + @SecondTABLE_SCHEMA + '.' + @SecondTABLE_NAME
    EXEC @return_status = master.dbo.TableExist @SecondTABLE_CATALOG, @SecondTABLE_SCHEMA, @SecondTABLE_NAME
    IF @return_status = 0
        BEGIN
            PRINT @table2 + ' : Table Not FOUND'
            RETURN 0
        END

    -- Compare the two tables
    DECLARE @sql AS NVARCHAR(MAX)
    SELECT @sql = '('
                + '(SELECT ''' + @table1 + ''' as _Table, * FROM ' + @FirstTABLE_CATALOG + '.' + @FirstTABLE_SCHEMA + '.' + @FirstTABLE_NAME + ')'
                + 'EXCEPT'
                + '(SELECT ''' + @table1 + ''' as _Table, * FROM ' + @SecondTABLE_CATALOG + '.' + @SecondTABLE_SCHEMA + '.' + @SecondTABLE_NAME + ')'
                + ')'
                + 'UNION'
                + '('
                + '(SELECT ''' + @table2 + ''' as _Table, * FROM ' + @SecondTABLE_CATALOG + '.' + @SecondTABLE_SCHEMA + '.' + @SecondTABLE_NAME + ')'
                + 'EXCEPT'
                + '(SELECT ''' + @table2 + ''' as _Table, * FROM ' + @FirstTABLE_CATALOG + '.' + @FirstTABLE_SCHEMA + '.' + @FirstTABLE_NAME + ')'
                + ')'
    DECLARE @wrapper AS NVARCHAR(MAX) = 'if exists (' + @sql + ')' + char(10) + '    (' + @sql + ')ORDER BY 2'
    Exec(@wrapper)
END
GO

.

USE [master]
GO

CREATE PROC [dbo].[TableExist]
    @TABLE_CATALOG nvarchar(256),
    @TABLE_SCHEMA nvarchar(256),
    @TABLE_NAME nvarchar(256)
    AS
BEGIN
    IF NOT EXISTS(SELECT name FROM sys.databases WHERE name=@TABLE_CATALOG)
        RETURN 0

    declare @result table (TABLE_SCHEMA nvarchar(256), TABLE_NAME nvarchar(256))
    SET NOCOUNT ON
    insert into @result EXEC('Select TABLE_SCHEMA, TABLE_NAME from ' + @TABLE_CATALOG  + '.INFORMATION_SCHEMA.COLUMNS')
    SET NOCOUNT OFF

    IF EXISTS(SELECT TABLE_SCHEMA, TABLE_NAME FROM @result
                WHERE TABLE_SCHEMA=@TABLE_SCHEMA AND TABLE_NAME=@TABLE_NAME)
        RETURN 1

    RETURN 0
END

GO

---

Although this question is for SQL 2018, which in the year 2021 might not be that old, when you use Azure Data Studio, there is an extension that can be installed called SQL Server Schema Compare that does this for you.