Compare Databases

HI,

I have two different SQL Server databases.
Is there any tool or softaware that can comper the two databases.

Meaning: is there any tool that can instruct me exactly what are the differences of those two ?? (tables, fields, ect')

Thanks in advance !!!!
kukiyaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arbertCommented:
This is a really good product:

http://www.red-gate.com/SQL_Compare.htm


Wouldn't hurt you to invest in a good modeling tool for future stuff (like Powerdesigner http://sybase.com or Erwin)...

Brett
0
jayeshshahCommented:
here's a script i use to compare 2 database existing on the same server.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO




CREATE             Procedure sp_DBCompare
(            @DB1 varchar (255),
            @DB2 varchar (255)
            )
AS
BEGIN
      DECLARE @Time datetime
      SET @Time = GetDate ()
      SET ANSI_NULLS ON

      SET ANSI_WARNINGS ON

      SET NOCOUNT ON
      if exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.TableLacking'))
            TRUNCATE table tempdb.dbo.TableLacking
            
      IF not exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.TableLacking'))
      BEGIN
            CREATE TABLE tempdb.dbo.TableLacking (
            Name1 varchar (255),
            Type1 varchar (5),
            Name2 varchar (255),
            Type2 varchar (5)
            )
      END

      if exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.ObjectLacking'))
            TRUNCATE table tempdb.dbo.ObjectLacking
            
      IF not exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.ObjectLacking'))
      BEGIN
            CREATE TABLE tempdb.dbo.ObjectLacking (
            Name1 varchar (255),
            Type1 varchar (5),
            Name2 varchar (255),
            Type2 varchar (5)
            )
      END

      if exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.TableDiff'))
            TRUNCATE table tempdb.dbo.TableDiff
            
      IF not exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.TableDiff'))
      BEGIN
            CREATE TABLE tempdb.dbo.TableDiff (
                  NomeTable1 varchar(255) NULL,
                  Column_name1 varchar (255) NULL ,
                  Type1 varchar (255) NULL ,
                  Computed1 tinyint NULL ,
                  Lenght1 int NULL ,
                  Prec1 varchar (255) NULL ,
                  Scale1 varchar (255) NULL ,
                  Nullable1 tinyint NULL ,
                  Collation1 varchar (255) NULL,
                  NomeTable2 varchar(255) NULL,
                  Column_name2 varchar (255) NULL ,
                  Type2 varchar (255) NULL ,
                  Computed2 tinyint NULL ,
                  Lenght2 int NULL ,
                  Prec2 varchar (255) NULL ,
                  Scale2 varchar (255) NULL ,
                  Nullable2 tinyint NULL ,
                  Collation2 varchar (255) NULL
            )
      END

      if exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.ObjForCursor'))
            TRUNCATE table tempdb.dbo.ObjForCursor
            
      IF not exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.ObjForCursor'))
      BEGIN
            CREATE TABLE tempdb.dbo.ObjForCursor (
            TableName varchar (255)
            )
      END
      if exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.Table1'))
            TRUNCATE table tempdb.dbo.Table1
            
      IF not exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.Table1'))
      BEGIN
            CREATE TABLE tempdb.dbo.Table1 (
                  Table_name varchar(255) NOT NULL,
                  Column_name varchar (255) NOT NULL ,
                  Type varchar (255) NOT NULL ,
                  Computed tinyint NOT NULL ,
                  Lenght int NOT NULL ,
                  Prec varchar (255) NULL ,
                  Scale varchar (255) NULL ,
                  Nullable tinyint NOT NULL ,
                  Collation varchar (255) NULL
            )
      END

      if exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.Table2'))
            TRUNCATE table tempdb.dbo.Table2
            
      IF not exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.Table2'))
      BEGIN
            CREATE TABLE tempdb.dbo.Table2 (
                  Table_name varchar(255) NOT NULL,
                  Column_name varchar (255) NOT NULL ,
                  Type varchar (255) NOT NULL ,
                  Computed tinyint NOT NULL ,
                  Lenght int NOT NULL ,
                  Prec varchar (255) NULL ,
                  Scale varchar (255) NULL ,
                  Nullable tinyint NOT NULL ,
                  Collation varchar (255) NULL
            )
      END

      DECLARE @Sql varchar(8000)
      
      SELECT @Sql =
      'INSERT INTO tempdb.dbo.TableLacking (Name1, Type1, Name2, Type2)
      SELECT       U1.name + ''.'' + T1.name, T1.type,
                  U2.name + ''.'' + T2.name, T2.type
      FROM ' + @DB1 + '.dbo.sysobjects T1
            INNER JOIN ' + @DB1 + '.dbo.sysusers U1 ON T1.uid = U1.uid
      FULL OUTER JOIN '+ @DB2 + '.dbo.sysobjects T2
            INNER JOIN ' + @DB2 + '.dbo.sysusers U2 ON T2.uid = U2.uid                        
      ON T1.name = T2.name AND T1.type = T2.type AND U1.name = U2.name
      WHERE (T1.name is null or T2.name is null)
            AND (T1.type = ''U'' OR T2.type = ''U'')
      ORDER By 1,2'

      EXEC (@Sql)
      
      IF (SELECT COUNT(*) FROM tempdb.dbo.TableLacking) > 0
      BEGIN
            SELECT * FROM tempdb.dbo.TableLacking
            PRINT 'Some table are lacking between databases ' + @DB1 + ' and ' + @DB2
            PRINT 'Please check the tempdb.dbo.TableLacking and synchronize it'
      END

      SELECT @Sql = '      INSERT INTO tempdb.dbo.ObjForCursor (TableName)
                              SELECT U1.name + ''.'' + T1.name
                              FROM ' + @DB1 + '.dbo.sysobjects T1
                                    INNER JOIN ' + @DB1 + '.dbo.sysusers U1 ON T1.uid = U1.uid
                              INNER JOIN ' + @DB2 + '.dbo.sysobjects T2
                                    INNER JOIN ' + @DB2 + '.dbo.sysusers U2 ON T2.uid = U2.uid                        
                              ON T1.name = T2.name AND T1.type = T2.type AND U1.name = U2.name
                        WHERE
                              (T1.type = ''U'' OR T2.type = ''U'')
                        ORDER BY 1'
      EXEC (@Sql)
      
      DECLARE @TableName varchar(255),
                  @Sql4Proc varchar(7000),
                  @Object1 varchar(250),
                  @Object2 varchar(250)

      DECLARE CurTable CURSOR STATIC FOR
            SELECT TableName FROM tempdb.dbo.ObjForCursor

      OPEN CurTable
      FETCH NEXT FROM CurTable INTO @TableName
      WHILE @@fetch_status <> -1
      BEGIN
      SELECT @Object1 = @DB1 + '.' + @TableName, @Object2 = @DB2 + '.' + @TableName
      /*
            SELECT @Sql4Proc =
            'INSERT INTO tempdb.dbo.TableDiff
             EXEC sp_TableCompare ''' + @DB1 + '.' + @TableName + ''', ''' + @DB2+ '.' + @TableName + ''''

            EXEC (@Sql4Proc)
      */
      BEGIN
            
                  DECLARE @numtypes varchar(80),
                              @objid1 int,
                              @objid2 int
            
                  SET      @numtypes = 'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney'
                  SET @TableName = PARSENAME(@TableName,1)
                  SELECT @Sql=      
                  'INSERT INTO tempdb.dbo.Table1 (Table_name, Column_name, Type, Computed, Lenght, Prec, Scale, Nullable,      Collation)
                  SELECT      ''' + @TableName + ''', C.name, T.name, C.iscomputed, convert(int, C.length),
                              case when charindex(T.name, ''' + @numtypes + ''') > 0
                                           then C.prec else 0 end,
                              case when charindex(T.name, ''' + @numtypes + ''') > 0
                                      then convert(char(5),OdbcScale(C.xtype,C.xscale))
                              else ''     '' end,
                              C.isnullable, C.collation
                  FROM ' + @DB1 + '.dbo.syscolumns C inner join ' + @DB1 + '.dbo.systypes T
                              ON      T.xtype = C.xtype AND T.usertype = C.usertype
                        INNER JOIN ' + @DB1 + '.dbo.sysobjects O
                              ON O.id = C.id
                        INNER JOIN ' + @DB1 + '.dbo.sysusers U
                              ON O.uid = U.uid            
                  WHERE O.name  = ''' + @TableName + ''' and U.name = ''' + PARSENAME(@Object1, 2) + '''and number = 0 ORDER BY colid'
--                  SELECT @Sql      
                  EXEC (@Sql)
                  
                  SELECT @Sql=      
                  'INSERT INTO tempdb.dbo.Table2 (Table_name, Column_name, Type, Computed, Lenght, Prec, Scale, Nullable,      Collation)
                  SELECT      ''' + @TableName + ''', C.name, T.name, C.iscomputed, convert(int, C.length),
                              case when charindex(T.name, ''' + @numtypes + ''') > 0
                                           then C.prec else 0 end,
                              case when charindex(T.name, ''' + @numtypes + ''') > 0
                                      then convert(char(5),OdbcScale(C.xtype,C.xscale))
                              else ''     '' end,
                              C.isnullable, C.collation
                  FROM ' + @DB2 + '.dbo.syscolumns C inner join ' + @DB2 + '.dbo.systypes T
                              ON      T.xtype = C.xtype AND T.usertype = C.usertype
                        INNER JOIN ' + @DB2 + '.dbo.sysobjects O
                              ON O.id = C.id
                        INNER JOIN ' + @DB2 + '.dbo.sysusers U
                              ON O.uid = U.uid            
                  WHERE O.name  = ''' + @TableName + ''' and U.name = ''' + PARSENAME(@Object2, 2) + '''and number = 0 ORDER BY colid'
--                  SELECT @Sql
                  EXEC (@Sql)
            
                  INSERT INTO tempdb.dbo.TableDiff
                  SELECT * FROM tempdb.dbo.Table1 T1 FULL OUTER JOIN tempdb.dbo.Table2 T2
                        ON T1.Column_name = T2.Column_name
                  WHERE (T1.Column_name is null or T2.Column_name is null)
                        OR (T1.Type <> T2.Type) OR (T1.Lenght <> T2.Lenght)
                        OR (T1.Prec <> T2.Prec) OR (T1.Nullable <> T2.Nullable)
                        OR (T1.Collation <> T2.Collation) OR (T1.Scale <> T2.Scale)

                  TRUNCATE table tempdb.dbo.Table1
                  TRUNCATE table tempdb.dbo.Table2

            END



      FETCH NEXT FROM CurTable INTO @TableName
      END
      CLOSE CurTable



      DEALLOCATE CurTable
      
      IF (SELECT COUNT(*) FROM tempdb.dbo.TableDiff) > 0
      BEGIN
            SELECT * FROM tempdb.dbo.TableDiff
            PRINT 'Some table are different between databases ' + @DB1 + ' and ' + @DB2
            PRINT 'Please check the tempdb.dbo.TableDiff and synchronize it'
      END


      SELECT @Sql =       
      'INSERT INTO tempdb.dbo.ObjectLacking (Name1, Type1, Name2, Type2)
      SELECT       U1.name + ''.'' + T1.name, T1.type,
                  U2.name + ''.'' + T2.name, T2.type
      FROM ' + @DB1 + '.dbo.sysobjects T1
            INNER JOIN ' + @DB1 + '.dbo.sysusers U1 ON T1.uid = U1.uid
      FULL OUTER JOIN '+ @DB2 + '.dbo.sysobjects T2
            INNER JOIN ' + @DB2 + '.dbo.sysusers U2 ON T2.uid = U2.uid                        
      ON T1.name = T2.name AND T1.type = T2.type AND U1.name = U2.name
      WHERE (T1.name is null or T2.name is null)
            AND (T1.type IN (''C'',''FN'',''IF'',''P'',''TF'',''TR'',''V'',''X'',''PK'',''F'', ''UK'')
                        OR T2.type IN (''C'',''FN'',''IF'',''P'',''TF'',''TR'',''V'',''X'', ''PK'', ''F'', ''UK'') )'

      
      EXEC (@Sql)
      
      SELECT @Sql =
      'INSERT INTO tempdb.dbo.ObjectLacking (Name1, Type1, Name2, Type2)
      SELECT  U1.name + ''.'' + T1.name + ''.'' + S1.name, ''IX'', U2.name + ''.'' + T2.name + ''.'' + S2.name, ''IX''
      FROM ' + @DB1 + '.dbo.sysobjects T1
            INNER JOIN ' + @DB1 + '.dbo.sysindexes S1       ON T1.id = S1.id
            INNER JOIN ' + @DB1 + '.dbo.sysusers U1       ON T1.uid = U1.uid
      FULL OUTER JOIN ' + @DB2 + '.dbo.sysobjects T2
            INNER JOIN ' + @DB2 + '.dbo.sysindexes S2      ON T2.id = S2.id
            INNER JOIN ' + @DB2 + '.dbo.sysusers U2            ON T2.uid = U2.uid
      ON S1.name = S2.name
      WHERE (S1.name is null or S2.name is null )
      AND (S1.indid between  0 and  255 and (S1.status & 64)=0 AND S1.keys is not null
                  OR
              S2.indid between  0 and  255 and (S2.status & 64)=0 AND S2.keys is not null)'


--      EXEC (@Sql)

      IF (SELECT COUNT(*) FROM tempdb.dbo.ObjectLacking) > 0
      BEGIN
            SELECT * FROM tempdb.dbo.ObjectLacking ORDER BY 1,2,3,4
            PRINT 'Some object are lacking between databases ' + @DB1 + ' and ' + @DB2
            PRINT 'Please check the tempdb.dbo.ObjectLacking and synchronize it'
      END
      PRINT 'Execution time: ' + CONVERT(varchar, DATEDIFF(ms,@Time, GetDate()) )+ ' ms'
      SET NOCOUNT OFF      
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


K'Regards

Jayesh
0
arbertCommented:
Nice script Jayesh.  The red hat tools do basically that, but they'll also generate "change scripts" so you can apply the changes to the database.....
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jayeshshahCommented:
thanx arbert ... i agree t o you but since this tool is not a freeware ... I prefer to use this script or more better i use Erwin. But Erwin doesnot give me the flexibility which i find in using scripts so i prefer to use scripts for things like comparing databases ... etc.

K'Regards

Jayesh
0
kukiyaAuthor Commented:
Thanks jayeshshah,

Your code looks promising..., but I believe it's too hard for me to understand.
I would appreciate it very much if you explain the steps you made.
Thanks in advance !!!!!:)
0
arbertCommented:
"freeware ... I prefer to use this script or more better i use Erwin"

I hope you don't consider Erwin freeware....Redhat is much more flexible than Erwin--Erwin is a modeler and redhat is specific for comparing objects...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.