Database comparison
I know this is 'cheating' but i was wondering if anybody had a quick way to compare two SQL2000 databases at an object level. I need to check the design of tables, code in Sp's etc. I think this can be done using systables, however i was wondering if anybody had done this and how easy it was.
All responses welcome.
Software/Hardware used:
Software/Hardware used:
ASKED:
March 9, 2005 4:21 PM
UPDATED:
March 10, 2005 11:23 AM
RELATED QUESTIONS
- Anyone know if any VMWare will run on Eee PC 1005HA, Has Atom N270 Processor, 1GB of Ram
- Is it possible using Embedded SQL or something else in RPG to directly update and MS SQL DB
- Exchange 2003 Enterprise Auto reply
- RPG Program calling Java
- How to easily perform date conversions in RPGLE (Including Substringing!)
Answer Wiki:
Hello,
I use the folowing script to get obj diffs between my SQL DB and those of the developpers.
These scripts only check U,V,IF,FN,TR,P,C existence
and diffs in columns or parameters.
Since all TSQL code is put into CVS.
You can easily add a script that checks systext.
Also, default column values are not checked.
The col_spec1/col_spec2 columns have a D and C indicator
D: column has default, C: column is computed
A dash - indicates no data.
Have fun.
Philippe.
To compare db between 2 different servers, link one of them to your local SQL Server.
In the following scripts 'GRN3' refers to the distant SQL Server.
-- * DOCS *
In the BOL, check
'linked servers, configuring'
'linked servers, login'
-- * Quick steps to configure a Linked server *
----------------
-- * Step 1 *
----------------
In Enterprise Manager (EM), connectes as "sa"
open DB, Security/Linked Servers
(right-clic)/"New linked server"
In the mew window, select Server type: (o) SQL Server
the rest is quite obvious.
----------------
-- * Step 2 *
----------------
create a new local login
(EM) Security/logins(right-click)/"New login"
+--- General
db_check_grn3
Authentication (o) SQL Server Authentication
Database: (local db to compare)
+--- Database Access
[x] (local db to compare)
---+
----------------
-- * STEP 3 * QA
in (QA) connect as "sa" or sysadmin/securityadmin member.
sp_addlinkedsrvlogin @rmtsrvname = 'GRN3'
, @useself = 'true'
, @locallogin = 'db_check_grn3'
, @rmtuser = '(login with dbo role)'
, @rmtpassword = '(its password)'
----------------
-- * STEP 4 * QA
connect as check_db_grn3
load script, replace .DISTANTDB. with remote db name.
File/Save as
Run
I usually copy/paste results into Excel and use the autofilter feature.
-- cmp_2_db_template.sql
select '-- Object existence check between DB '+db_name()+' and grn3.DISTANTDB.' ;
select CASE WHEN B1.name IS NULL THEN 'NEW'
WHEN B2.name IS NULL THEN 'OLD'
ELSE '===' END as obj_exists
, coalesce( B1.type, B2.type) as obj_type
, user_name(B1.uid)+'.'+B1.name as obj_name1
, U2.name+'.'+B2.name as obj_name2
from sysobjects B1
full outer join grn3.DISTANTDB.dbo.sysobjects B2
on B1.name = B2.name and B1.type = B2.type
left outer join grn3.DISTANTDB.dbo.sysusers U2
on B2.uid=U2.uid
where ( B1.type in ('U','V','FN','IF','P','TR','C') OR B1.type IS NULL)
and ( B2.type in ('U','V','FN','IF','P','TR','C') OR B2.type IS NULL)
order by 2,3
select '-- Column/Param check for objects existing in both db' ;
select
CASE WHEN C1.type != C2.type THEN 'type'
WHEN C1.length != C2.length THEN 'len'
WHEN coalesce(C1.isnullable,0) != coalesce(C2.isNullable,0) THEN 'null'
WHEN coalesce(C1.isOutParam,0) != coalesce(C2.isOutParam,0) THEN 'outparam'
WHEN sign(C1.cdefault) != sign(C2.cdefault) THEN 'default'
WHEN C1.colid != C2.colid THEN '-moved'
ELSE '===' END
as col_status
, C1.name as col_name
, CASE WHEN P1.type in ('U','V') THEN 'COL' ELSE 'PARM' END as col_param
, P1.type as obj_type
, user_name(P1.uid)+'.'+P1.name as obj_name1
, C1.colid as pos1
, U2.name+'.'+P2.name as obj_name2
, C2.colid as pos2
, cast(C1.colid as char(4))
+ ' '+S1.name+'('+cast(C1.length as varchar(10))
+') '+CASE C1.isnullable WHEN 1 THEN 'NULL' WHEN 0 THEN 'NOTNULL' ELSE '-' END
+ ' '+ CASE C1.isOutParam WHEN 1 THEN 'OUT' WHEN 0 THEN 'IN' ELSE '-' END
+ ' '+ CASE WHEN C1.cdefault >0 THEN 'D' ELSE '-' END
+ ' '+ CASE WHEN coalesce(C1.isComputed,0) =0 THEN '-' ELSE 'C' END
as col_specs1
, cast(C2.colid as char(4))
+ ' '+ S2.name+'('+cast(C2.length as varchar(10))
+') '+ CASE C2.isnullable WHEN 1 THEN 'NULL' WHEN 0 THEN 'NOTNULL' ELSE '-' END
+ ' '+ CASE C2.isOutParam WHEN 1 THEN 'OUT' WHEN 0 THEN 'IN' ELSE '-' END
+ ' '+ CASE WHEN C2.cdefault >0 THEN 'D' ELSE '-' END
+ ' '+ CASE WHEN coalesce(C2.isComputed,0) =0 THEN '-' ELSE 'C' END
as col_specs2
FROM systypes S1
inner join syscolumns C1
on C1.xtype= S1.xtype and C1.xusertype=S1.xusertype --and S1.typestat = C1.typestat
inner join sysobjects P1
on C1.id = P1.id
AND P1.type in ('U','V','FN','IF','P')
, grn3.DISTANTDB.dbo.systypes S2
inner join grn3.DISTANTDB.dbo.syscolumns C2
on C2.xtype= S2.xtype and C2.xusertype=S2.xusertype
inner join grn3.DISTANTDB.dbo.sysobjects P2
on P2.id = C2.id
AND P2.type in ('U','V','FN','IF','P')
inner join grn3.DISTANTDB.dbo.sysusers U2
on P2.uid=U2.uid
WHERE (C1.name = C2.name OR (C1.colid=0 AND C2.colid=0))
and P1.name= P2.name and P1.type=P2.type
UNION
select'OLD'
as col_status
, C1.name as col_name
, CASE WHEN P1.type in ('U','V') THEN 'COL' ELSE 'PARM' END
, P1.type as obj_type
, user_name(P1.uid)+'.'+P1.name as obj_name1
, C1.colid as pos1
, U2.name+'.'+P2.name as obj_name2
, 0 -- C2.colid as pos2
, cast(C1.colid as char(4))
+ ' '+S1.name+'('+cast(C1.length as varchar(10))
+') '+CASE C1.isnullable WHEN 1 THEN 'NULL' WHEN 0 THEN 'NOTNULL' ELSE '-' END
+ ' '+ CASE C1.isOutParam WHEN 1 THEN 'OUT' WHEN 0 THEN 'IN' ELSE '-' END
+ ' '+ CASE WHEN C1.cdefault >0 THEN 'D' ELSE '-' END
+ ' '+ CASE WHEN coalesce(C1.isComputed,0) =0 THEN '-' ELSE 'C' END
as col_specs1
, null
FROM systypes S1
inner join syscolumns C1
on C1.xtype= S1.xtype and C1.xusertype=S1.xusertype
inner join sysobjects P1
on C1.id = P1.id
AND P1.type in ('U','V','FN','IF','P')
inner join grn3.DISTANTDB.dbo.sysobjects P2
on P1.type=P2.type AND P1.name=P2.name
inner join grn3.DISTANTDB.dbo.sysusers U2
on P2.uid=U2.uid
WHERE NOT EXISTS(SELECT 0 FROM grn3.DISTANTDB.dbo.syscolumns C2
WHERE C2.id = P2.id AND (C1.name = C2.name OR (C1.colid=0 AND C2.colid=0)))
UNION
select 'NEW'
, C2.name as col_name
, CASE WHEN P2.type in ('U','V') THEN 'COL' ELSE 'PARM' END
, P2.type
, U2.name+'.'+P2.name as obj_name2 -- should be null, but eases reading
, C2.colid as pos1 -- should be null, but eases reading
, null
, null
, null
, cast(C2.colid as char(4))
+ ' '+ S2.name+'('+cast(C2.length as varchar(10))
+') '+ CASE C2.isnullable WHEN 1 THEN 'NULL' WHEN 0 THEN 'NOTNULL' ELSE '-' END
+ ' '+ CASE C2.isOutParam WHEN 1 THEN 'OUT' WHEN 0 THEN 'IN' ELSE '-' END
+ ' '+ CASE WHEN C2.cdefault >0 THEN 'D' ELSE '-' END
+ ' '+ CASE WHEN coalesce(C2.isComputed,0) =0 THEN '-' ELSE 'C' END
FROM grn3.DISTANTDB.dbo.systypes S2
inner join grn3.DISTANTDB.dbo.syscolumns C2
on C2.xtype= S2.xtype and C2.xusertype=S2.xusertype
inner join grn3.DISTANTDB.dbo.sysobjects P2
on C2.id = P2.id
AND P2.type in ('U','V','FN','IF','P')
inner join grn3.DISTANTDB.dbo.sysusers U2
on P2.uid=U2.uid
inner join sysobjects P1
on P1.type=P2.type AND P1.name=P2.name
WHERE NOT EXISTS(SELECT 0 FROM syscolumns C1
WHERE C1.id = P1.id AND (C1.name = C2.name OR (C1.colid=0 AND C2.colid=0)))
order by 4,5,6
To see all answers submitted to the Answer Wiki: View Answer History.
There are several third party tools that will do this much easier for you. Most if not all have a trial period for their software so for just one use you could use the trial to get your answers. Try RedGate’s SQLCompare. http://www.red-gate.com/products.htm. Also, a product called xSQL does just as good a job and its free. (no trial timeout) http://www.x-sql.com/default.aspx