What are you looking for ?
Home » » Database Compare

Database Compare

Posted by Wael.Medhat Posted on 8:16 am with No comments
Sometimes we need to compare tables and/or data to know what was changed. How to compare data between two table in different databases using Sql Server?Or even compare between two databases to know the different in tables,views or stored procedure

Compare Data Between Tables

select *
from (
      select *
      from DB1.dbo.Table
      except
      select *
      from DB2.dbo.Table
     ) as T
union all
select *
from (
      select *
      from DB2.dbo.Table
      except
      select *
      from DB1.dbo.Table
     ) as T

Compare Extra Columns Between Tables

select * 
from (
      select name, column_id
     FROM DB1.sys.columns
WHERE object_id = OBJECT_ID('DB1.dbo.Table1')
      except
      select  name, column_id
     FROM DB2.sys.columns
WHERE object_id = OBJECT_ID('DB2.dbo.Table2')
     ) as T
union all
select * 
from (
     select  name, column_id
     FROM DB2.sys.columns
WHERE object_id = OBJECT_ID('DB2.dbo.Table2')
      except
      select name, column_id
     FROM DB1.sys.columns
WHERE object_id = OBJECT_ID('DB1.dbo.Table1')
     ) as T

Compare Datatype Between Tables

select * 
from (
 SELECT        c.column_id, c.name AS 'Column Name', t.name AS 'Data type', c.max_length AS 'Max Length', c.is_nullable
FROM            sys.columns AS c INNER JOIN
                         sys.types AS t ON c.user_type_id = t.user_type_id
WHERE        (c.object_id = OBJECT_ID('DB1.dbo.Table1'))
      except
SELECT        c.column_id, c.name AS 'Column Name', t.name AS 'Data type', c.max_length AS 'Max Length', c.is_nullable
FROM            sys.columns AS c INNER JOIN
                         sys.types AS t ON c.user_type_id = t.user_type_id
WHERE        (c.object_id = OBJECT_ID('DB2.dbo.Table2'))
) as T

 union all

 select * 
from (
SELECT        c.column_id, c.name AS 'Column Name', t.name AS 'Data type', c.max_length AS 'Max Length', c.is_nullable
FROM            sys.columns AS c INNER JOIN
                         sys.types AS t ON c.user_type_id = t.user_type_id
WHERE        (c.object_id = OBJECT_ID('DB2.dbo.Table2'))
      except
SELECT        c.column_id, c.name AS 'Column Name', t.name AS 'Data type', c.max_length AS 'Max Length', c.is_nullable
FROM            sys.columns AS c INNER JOIN
                         sys.types AS t ON c.user_type_id = t.user_type_id
WHERE        (c.object_id = OBJECT_ID('DB1.dbo.Table1'))

) as T


Compare Between Existing Tables 

select *
from (
      select name
      from   DB1.sys.tables
      except
      select name
      from  DB2.sys.tables
     ) as T
union all
select *
from (
      select name
      from  DB2.sys.tables
      except
      select name
      from DB1.sys.tables
     ) as T


Compare Between Existing Views

select *
from (
      select name
      from   DB1.sys.views
      except
      select name
      from  DB2.sys.views
     ) as T
union all
select *
from (
      select name
      from  DB2.sys.views
      except
      select name
      from DB1.sys.views
     ) as T


Compare Between Existing Procedures

select *
from (
      select name
      from   DB1.sys.procedures
      except
      select name
      from  DB2.sys.procedures
     ) as T
union all
select *
from (
      select name
      from  DB2.sys.procedures
      except
      select name
      from DB1.sys.procedures
     ) as T

Share this article :
Labels:

Post a Comment

Flag Counter

Social Profile

 
Copyright x 2011. By Wael Medhat - All Rights Reserved