Solved

Compare Databases

Posted on 2003-12-07
6
786 Views
Last Modified: 2007-12-19
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 !!!!
0
Comment
Question by:kukiya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 9892661
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
 
LVL 5

Expert Comment

by:jayeshshah
ID: 9892675
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
 
LVL 34

Expert Comment

by:arbert
ID: 9892682
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 5

Expert Comment

by:jayeshshah
ID: 9894802
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
 

Author Comment

by:kukiya
ID: 9895173
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
 
LVL 34

Accepted Solution

by:
arbert earned 50 total points
ID: 9896438
"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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question