Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73

    Unanswered: Database differences

    We have a database that when an update is released (and this is very often) the release notes don't cover most of the actual changes. Every time groups of our custom queries and reports get broken due to database changes. Does anyone know how to compare two databases and get a report of the differences between them? I can either have the two versions on the same server or on different servers if that makes a difference.

    I'm hoping for something where you input @oldversion, @newversion

    and return is

    @oldversion, tblname, fieldname, char(8)
    @newversion, tblname, fieldname, varchar(8)
    @oldversion, tblname, [Null], [Null]
    @newversion, tblname, fieldname, int
    @oldversion, [Null]
    @newversion, tblname

    also any changes in dependancies

    Thanks
    Brent

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    --sp_addlinkedserver @server = 'NJROS1D98'
    --sp_addlinkedserver @server = 'NJROS1A144'
    --select * from sysservers
    
    --select * from njros1a144.vigncontent.dbo.TBL_EDG_PAGE
    
    --sp_addlinkedserver 
    --    'NJEPWA103', 
    --    'Oracle', 
    --    'MSDAORA', 
    --    'ORC1'
    
    --Select * from NJEPWA103.ORC1.dbo.sysobjects
    
    /* Objects in Pru Missing in NetCo */
    Select 'Table Objects in Pru but are not in NetCo'
    select Left(a.name,30), a.refdate from sysobjects a 
    Where a.xtype = 'U' 
    and a.name like 'TBL%'
    and Not Exists (Select 1 From njros1a144.vigncontent.dbo.sysobjects b where a.name = b.name)
    
    /* Objects in NetCo Missing in Pru */
    Select 'Table Objects in NetCo but are not in Pru' 
    select Left(a.name,30), a.refdate from njros1a144.vigncontent.dbo.sysobjects a 
    Where a.xtype = 'U' 
    and a.name like 'TBL%'
    and Not Exists (Select 1 From sysobjects b where a.name = b.name)
    
    /*  Column Differences */
    
    Select 'Column Differences between like named tables'
    
    select Left(x.TabName,30) as TableName, Left(x.ColName,30) as ColumnName
         , Left(x.DataType,15) as PruDataType, x.length as PruLength, x.refdate as PruRefDate
         , Left(y.DataType,15) as NetCoDataType, y.length As NetCoLength,  y.refdate as NetCoRefDate
    from 
         ( Select a.name as TabName, b.name as ColName, b.length, c.name as DataType, a.refdate
             from sysobjects a, syscolumns b, systypes c
    	where a.id = b.id 
              and b.xusertype = c.xusertype
              and a.xtype = 'U' and a.name like 'TBL%') As x
    ,     ( Select a.name as TabName, b.name as ColName, b.length,  c.name as DataType, a.refdate
              from njros1a144.vigncontent.dbo.sysobjects a, njros1a144.vigncontent.dbo.syscolumns b, njros1a144.vigncontent.dbo.systypes c
    	where a.id = b.id and a.xtype = 'U'
              and b.xusertype = c.xusertype 
              and a.name like 'TBL%') As y
    Where  x.TabName = y.TabName 
      and  x.ColName = y.ColName 
      and (x.length <> y.length or x.DataType <> y.DataType)
    
    /*  Column Differences */
    Select 'Column in Pru.com not in NetCo'
    
    Select Left(a.name,30) as TableName, Left(b.name,30) as ColumnName, b.length, c.name, a.refdate
      from sysobjects a, syscolumns b, systypes c
     where a.id = b.id 
       and b.xusertype = c.xusertype
       and a.xtype = 'U' 
       and a.name like 'TBL%'
       and Not Exists (
            Select 1
              from njros1a144.vigncontent.dbo.sysobjects d, njros1a144.vigncontent.dbo.syscolumns e
    	 where d.id = e.id 
    	   and a.xtype = 'U'
               and a.name like 'TBL%'
               and a.name = d.name
      	   and b.name = e.name)
    Order by 1, 2
    
    /*  Column Differences */
    Select 'Column in NetCo not in Pru.com'
    
    Select Left(a.name,30) as TableName, Left(b.name,30) as ColumnName, b.length, c.name, a.refdate
      from njros1a144.vigncontent.dbo.sysobjects a, njros1a144.vigncontent.dbo.syscolumns b, njros1a144.vigncontent.dbo.systypes c
     where a.id = b.id 
       and b.xusertype = c.xusertype
       and a.xtype = 'U' 
       and a.name like 'TBL%'
       and Not Exists (
            Select 1
              from sysobjects d, syscolumns e
    	 where d.id = e.id 
    	   and a.xtype = 'U'
               and a.name like 'TBL%'
               and a.name = d.name
      	   and b.name = e.name)
    Order by 1, 2
    
    
    
    
    
    --Select 'Table Objects that are still in use in both NetCo and Pru'
    --select Left(a.name,30), a.refdate from sysobjects a, njros1a144.vigncontent.dbo.sysobjects b where a.name = b.name and a.xtype = 'U'
    Gotta change this to use the INFORMATION_SCHEMA views....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Do you mean replace sysobj with Information_schema?

    Not clear on where the modification you mention at the end takes place, can clearly see where I have to replace your server.db names with my own.

    Thanks,

    Brent

  4. #4
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Do you mean replace sysobj with Information_schema?

    Not clear on where the modification you mention at the end takes place, can clearly see where I have to replace your server.db names with my own.

    Thanks,

    Brent

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No....for now, just replace

    njros1a144.vigncontent.dbo

    Change the Server.database.owner...that you want to compare one to the other

    And run the code on the database that needs to be compared...

    just cut and paste the code in to a query analyser window...

    good luck...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I just wanted to change the code so it uses the schemas, because the damn ccatalog is sooooo convaluted....I wrote this awhile back when I didn't know about the views...


    You gotta do a sp_helptext against sp_help...

    very funny comments in there....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    I was just prepping a response for this:

    Changing the names for our names looks to work great, thank you very much. One further question, is there a way to use variable for the dbname/s as follows:

    Declare
    @newver
    @oldver

    Set @newver = newverion
    set @oldver = oldserver.oldversion

    use @newver

    Below is what I'm running now. For the above example I would like to replace all of the 'oldserver.oldversion' entries to use the @oldver variable, but when I try it query analyser reads the path as @oldver instead of the set value. Any ideas?

    Again thanks a ton

    Brent

    Select 'Table Objects in ' + (Select lkpversion.version from lkpversion) + ' but are not in ' +(Select version from oldserver.oldversionname.dbo.lkpversion)
    select Left(a.name,30) as TableName, a.refdate from sysobjects a
    Where a.xtype = 'U'
    and a.name like 'TBL%'
    and Not Exists (Select 1 From oldserver.oldversionname.dbo.sysobjects b where a.name = b.name)

    /* Objects in old Version Missing in New Version */
    Select 'Table Objects in ' + (select version from oldserver.oldversionname.dbo.lkpversion) +' but are not in '+ (select lkpversion.version from lkpversion)
    select Left(a.name,30) as TableName, a.refdate from oldserver.oldversionname.dbo.sysobjects a
    Where a.xtype = 'U'
    and a.name like 'TBL%'
    and Not Exists (Select 1 From sysobjects b where a.name = b.name)

    /* Column Differences */

    Select 'Column Differences between like named tables'

    select Left(x.TabName,30) as TableName, Left(x.ColName,30) as ColumnName
    , Left(x.DataType,15) as NewVerType, x.length as NewVerLength, x.refdate as NewVerRefDate
    , Left(y.DataType,15) as OldVerType, y.length As OldVerLength, y.refdate as OldVerRefDate
    from
    ( Select a.name as TabName, b.name as ColName, b.length, c.name as DataType, a.refdate
    from sysobjects a, syscolumns b, systypes c
    where a.id = b.id
    and b.xusertype = c.xusertype
    and a.xtype = 'U' and a.name like 'TBL%') As x
    , ( Select a.name as TabName, b.name as ColName, b.length, c.name as DataType, a.refdate
    from oldserver.oldversionname.dbo.sysobjects a, oldserver.oldversionname.dbo.syscolumns b, oldserver.oldversionname.dbo.systypes c
    where a.id = b.id and a.xtype = 'U'
    and b.xusertype = c.xusertype
    and a.name like 'TBL%') As y
    Where x.TabName = y.TabName
    and x.ColName = y.ColName
    and (x.length <> y.length or x.DataType <> y.DataType)

    /* Column Differences */
    Select 'Column in ' + (Select lkpversion.version from lkpversion) +' not in ' + (Select version from oldserver.oldversionname.dbo.lkpversion)

    Select Left(a.name,30) as TableName, Left(b.name,30) as ColumnName, b.length, c.name, a.refdate
    from sysobjects a, syscolumns b, systypes c
    where a.id = b.id
    and b.xusertype = c.xusertype
    and a.xtype = 'U'
    and a.name like 'TBL%'
    and Not Exists (
    Select 1
    from oldserver.oldversionname.dbo.sysobjects d, oldserver.oldversionname.dbo.syscolumns e
    where d.id = e.id
    and a.xtype = 'U'
    and a.name like 'TBL%'
    and a.name = d.name
    and b.name = e.name)
    Order by 1, 2

    /* Column Differences */
    Select 'Column in '+ (Select version from oldserver.oldversionname.dbo.lkpversion) + ' but are not in ' +(Select lkpversion.version from lkpversion)

    Select Left(a.name,30) as TableName, Left(b.name,30) as ColumnName, b.length, c.name, a.refdate
    from oldserver.oldversionname.dbo.sysobjects a, oldserver.oldversionname.dbo.syscolumns b, oldserver.oldversionname.dbo.systypes c
    where a.id = b.id
    and b.xusertype = c.xusertype
    and a.xtype = 'U'
    and a.name like 'TBL%'
    and Not Exists (
    Select 1
    from sysobjects d, syscolumns e
    where d.id = e.id
    and a.xtype = 'U'
    and a.name like 'TBL%'
    and a.name = d.name
    and b.name = e.name)
    Order by 1, 2

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You'd need to do dynamic SQL with an EXEC(@sql)

    What is lkpversion?

    Those have to stay as the system tables I coded....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Originally posted by Brett Kaiser
    You'd need to do dynamic SQL with an EXEC(@sql)

    What is lkpversion?

    Those have to stay as the system tables I coded....
    lkpversion is a table that contains the version number of the software.

    So what I get is a descriptive field lik:

    tables in version 3.8 not in version 3.7

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •