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--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...|||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|||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|||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...|||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...|||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|||You'd need to do dynamic SQL with an EXEC(@.sql)
What is lkpversion?
Those have to stay as the system tables I coded...|||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
