Table compare - comments would be valued

Last Post 31 Mar 2008 09:12 PM by scotchp. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
riotinto
New Member
New Member

--
12 Mar 2008 07:37 PM
Hi All

I'm using this stored procedure as part of a project to align a series of databases. I call it from an interface that passes the various parameters. I'm aware of various comparison tools however wanted a cheap and simple method to allow users to scrutinise differences.


I'd appreciate any feedback from users who have done similar things or have similar needs. The more robust it is and cleanly coded, etc the better.

thanks in advance
bevan
-----------------
if object_id('QTFN_ITEM_IN_LIST') is not null
drop function QTFN_ITEM_IN_LIST
go


create FUNCTION [dbo].[QTFN_ITEM_IN_LIST]
(
@Str as varchar(4000),
@Delimiter char(1)
)
returns table
as




return (
select NUM as ORDINAL
,[dbo].QFN_ITEM_IN_LIST(NUM, @Str, @Delimiter) as ITEM
from QT_PIVOT
where NUM > 0 and NUM <= len(@Str) - len(replace(@Str , @Delimiter, '')) + 1
)
go
-----------------
if object_id('SP_DISPLAY_DIFFERENCES') is not null
drop procedure SP_DISPLAY_DIFFERENCES
go

create procedure SP_DISPLAY_DIFFERENCES
@PK_Fields varchar(max),
@NONPK_Fields varchar(max),
@ALIAS1 varchar(100),
@ALIAS2 varchar(100),
@SQL1 varchar(max),
@SQL2 varchar(max),
@MakeTable char(1) = 'F',
@Debug char(1) = 'F'

as
/*
Name: SP_DISPLAY_DIFFERENCES

Purpose: Display the differences between records in two select statements

Author: Bevan Ward

Pseudo code: 1. Build SQL components for outer join of the form -

select <category of error>,
<primary key fields>,
<non-primary key values - ONLY where differences in form value 1/value 2>
from (SQL1) {Alias1} full outer join (SQL2) {Alias2}
on <primary key field(s)>
where <either primary key field(s) are null> OR <Difference in Non-Primary key fields>
order by <category of error>, <primary key fields>


Syntax: exec SP_DISPLAY_DIFFERENCES
@PK_Fields = '<Comma separated list of primary key fields (NO SPACES BETWEEN FIELDS -can be spaces in field names)>'
,@NONPK_Fields = '<as above non-primary key fields>'
,@ALIAS1 = '<Alias for left table for join - used in reporting difference>'
,@ALIAS2 = '<As above for right table>'
,@SQL1 = '<SQL for select of left table>'
,@SQL2 = '<SQL for select of right table - (If a ' is required parse as literal i.e. '' )>'
,@MakeTable = 'F'
,@Debug = '<T/F>'

Example 1: Compare GEOLOGYCODESECONDAYR table from different DATABASES

exec SP_DISPLAY_DIFFERENCES
@PK_Fields = 'NAME'
,@NONPK_Fields = 'DESCRIPTION,PRIMARYCODE'
,@ALIAS1 = 'GS'
,@ALIAS2 = 'NAR'
,@SQL1 = 'select NAME, PRIMARYCODE, DESCRIPTION from GLOBAL_STUB..GEOLOGYCODESECONDARY'
,@SQL2 = 'select NAME, PRIMARYCODE, DESCRIPTION from COAL_STUB..GEOLOGYCODESECONDARY'
,@MakeTable = 'F'
,@Debug = 'T'

Example 2: Compare GEOLOGYCODESECONDARY table out of registry for two different databases

exec SP_DISPLAY_DIFFERENCES
@PK_Fields = 'NAME'
,@NONPK_Fields = 'DESCRIPTION,PRIMARYCODE,DATATYPE'
,@ALIAS1 = 'Ref DB'
,@ALIAS2 = 'Test DB'
,@SQL1 = 'select DATABASE_NAME,min(case when FIELD_NAME = ''NAME'' then VALUE end) NAME,min(case when FIELD_NAME = ''DESCRIPTION'' then VALUE end) DESCRIPTION,min(case when FIELD_NAME = ''PRIMARYCODE'' then VALUE end) PRIMARYCODE,min(case when FIELD_NAME = ''DATATYPE'' then VALUE end) DATATYPE FROM RTX_PROFILE_COMPILE_REF_DATA A INNER JOIN (select DBID, DATABASE_NAME from RTX_PROFILE_COMPILE_RUN_HISTORY where DATABA
SQLUSA
New Member
New Member

--
13 Mar 2008 10:36 PM
Do you want to risk your job using your own invention?

Or use a professional tool like SQL Compare from Red-Gate?

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/ SQL Server Training
riotinto
New Member
New Member

--
19 Mar 2008 02:45 PM
Hi Kalman

My job is fine - thanks for taking the time

Cheers
sleakbug
New Member
New Member

--
20 Mar 2008 09:27 AM
I licensed this tool, Advanced Query Tool
( at http://www.querytool.com )
due to its ability to compare table schemas across different platforms - SQL Server 2000 and iSeries (AS/400) DB2 in my case.

It will also compare (single-table to single-table) graphically by column cardinal order or column name order and graphically display differences that can be printed or exported.

Excellent tool all in all especially when working with data positioned on multiple platforms.

scotchp
New Member
New Member

--
31 Mar 2008 09:12 PM
www.redgate.com


Acceptable Use Policy
---