use sybsystemprocs
go
drop proc sp_gen_cmpscript
go
print    'sp_gen_cmpscript'
SETUSER  'dbo'
go

create proc sp_gen_cmpscript   
    @pds        varchar(30) = null  
   ,@rds        varchar(30) = null  
   ,@pdb        varchar(30) = null  
   ,@rdb        varchar(30) = null  
   ,@puser      varchar(30) = null  
   ,@ruser      varchar(30) = null  
   ,@ppwd       varchar(30) = null  
   ,@rpwd       varchar(30) = null  
   ,@os         char(1)     = "W"  
   ,@pause      int         = null  
   ,@echoon     int         = null  
   ,@showcounts int         = null  
   ,@truncate   int         = null
   ,@recon      varchar(30) = "NONE"
   ,@visual     int         = 1
   ,@help       int         = null  
as  
 
if (isnull(@help,0) = 0) 
begin 
  if (isnull(@pds,"~") = "~")  
  begin 
    print "Primary server required - @pds" 
    select @help = 1  
  end 
  else 
  if (isnull(@rds,"~") = "~")  
  begin 
    print "Replicate server required - @rds" 
    select @help = 1  
  end 
  else 
  if (isnull(@pdb,"~") = "~") 
  begin 
    print "Primary dbname required - @pdb" 
    select @help = 1  
  end 
  else 
  if (isnull(@rdb,"~") = "~") 
  begin 
    print "Replicate dbname required - @rdb" 
    select @help = 1  
  end 
  else 
  if (isnull(@puser,"~") = "~")  
  begin 
    print "Primary usernamer required - @puser" 
    select @help = 1  
  end 
  else 
  if (isnull(@ruser,"~") = "~")  
  begin 
    print "Replicate username required - @ruser" 
    select @help = 1  
  end 
  else 
  if (isnull(@ppwd,"~") = "~") 
  begin 
    print "Primary user password required - @ppwd" 
    select @help = 1  
  end 
  else 
  if (isnull(@rpwd,"~") = "~") 
  begin 
    print "Replicate user password required - @rpwd" 
    select @help = 1  
  end 
end 
 
if (isnull(@help,0) = 1)  
begin  
    print "sp_gen_cmpscript - Generates output for a Windows/Unix script file to generate" 
    print "                   auto-scripts for rs_subcmp"  
    print ""  
    print "Usage: sp_gen_cmpscript @pds ,@rds ,@pdb ,@rdb ,@puser ,@ruser ,@ppwd ,@rpwd [,@os] [,@pause]" 
    print "                        [,@echoon] [,@showcounts] [,@truncate] [,@visual] [,@recon] [,@recon]"  
    print "Where:"  
    print " @pds        - Primary dataserver name" 
    print " @rds        - Replicate dataserver name" 
    print " @pdb        - Primary database name" 
    print " @rdb        - Replicate database name" 
    print " @puser      - Username for Primary data server"  
    print " @ruser      - Username for Replicate data server"  
    print " @ppwd       - Password for '@puser' user on Primary data server"  
    print " @rpwd       - Password for '@ruser' user on Replicate data server"  
    print " @os         - O/S to generate script for - 'U'=Unix or 'W'=Windows - default is (W)"  
    print " @pause      - If 1 (true), add a 'pause' before & after the 'rs_subcmp' command-line"  
    print " @echoon     - If 1 (true), echo each command executed - valid for @os=(W)indows only"  
    print " @showcounts - If 1, show Primary and Replicate table counts before / after Sync. (default)"  
    print " @truncate   - If 1, always truncate Replicate table before Sync - use with care"
    print " @visual     - If 1, Set Visual comparison ON for subcmp (1=default)"
    print " @recon      - Method for reconcile :"
    print "               'RECONCILE'       - Reconcile without primary re-verify"
    print "               'RECONCILE_CHECK' - Reconcile with Primary re-verification"
    print "               'NONE'            - No reconciliation - check only (DEFAULT)"

    return 0  
end  
 
select @os=upper(isnull(@os,"W"))  
select @pause=isnull(@pause,0)  
select @echoon=isnull(@echoon,0)  
select @showcounts=isnull(@showcounts,1)  
select @truncate=isnull(@truncate,0)  
  
declare   
  @tabid      int  
 ,@tabname    varchar(30)  
 ,@delimit    varchar(1)  
 ,@echop      varchar(1) 
  
set nocount on  
  
create table #table_list   
(  
   id int  
  ,name varchar(30)  
)  
  
insert #table_list select id,name from sysobjects where type = "U" 
and name not like "rs_%" 
and name not like "spt_%" 
order by id 
 
if (@os="W")  
  begin  
    select @delimit = NULL  
    if (@echoon = 0)   
       select @echop = "@"  
    else  
       select @echop = NULL  
  end  
else  
  begin  
    select @echoon = 1  
    select @delimit = '"'  
    select @echop = NULL  
  end  
  
select @tabid = 0  
while (select min(id) from #table_list where id > @tabid) != NULL  
begin  
   select @tabid = min(id)  
   from #table_list where id > @tabid  
   select @tabname = name   
   from #table_list where @tabid = id  
  
   if @os = 'W' 
   begin 
  print "REM ===============================================" 
     print "REM Start of %1!",@tabname 
     print "REM ===============================================" 
   end 
   else 
   begin 
     print "# ===============================================" 
     print "# Start of %1!",@tabname 
     print "# ===============================================" 
   end 
   print "%4!echo %1!Comparing / Synchronizing table %3!.%2!",@delimit,@delimit,@tabname,@echop  
   if (@truncate = 1)  
   begin  
     print "%3!echo %1!set nocount on%2! > count.sql",@delimit,@delimit,@echop  
     print "%3!echo %1!use %4!%2! >> count.sql",@delimit,@delimit,@echop,@pdb 
     print "%3!echo %1!go%2! >> count.sql",@delimit,@delimit,@echop  
     print "%3!echo %1!select count(*) from %4!%2! >> count.sql",@delimit,@delimit,@echop,@tabname  
     print "%3!echo %1!go%2! >> count.sql",@delimit,@delimit,@echop  
     print "%3!echo %1!Primary table count:%2!",@delimit,@delimit,@echop  
     print "%1!isql -w300 -U%4! -P%2! -S%3! -i count.sql",@echop,@ppwd,@pds,@puser 
     print "%3!echo %1!Replicate table count:%2!",@delimit,@delimit,@echop  
     print "%1!isql -w300 -U%4! -P%2! -S%3! -i count.sql",@echop,@rpwd,@rds,@ruser 
     print "%3!echo %1!use %4!%2! > trunc.sql",@delimit,@delimit,@echop,@rdb 
     print "%3!echo %1!go%2! >> trunc.sql",@delimit,@delimit,@echop  
     print "%3!echo %1!truncate table %4!%2! >> trunc.sql",@delimit,@delimit,@echop,@tabname  
     print "%3!echo %1!go%2! >> trunc.sql",@delimit,@delimit,@echop  
     print "%3!echo %1!select truncated=count(*) from %4!%2! >> trunc.sql",@delimit,@delimit,@echop,@tabname  
     print "%3!echo %1!go%2! >> trunc.sql",@delimit,@delimit,@echop  
     print "%1!isql -w300 -U%4! -P%2! -S%3! -i trunc.sql",@echop,@rpwd,@rds,@ruser 
   end  
   print "%4!echo %1!set nocount on%2! > %3!.sql",@delimit,@delimit,@tabname,@echop  
   print "%4!echo %1!use %5!%2! >> %3!.sql",@delimit,@delimit,@tabname,@echop,@pdb 
   print "%4!echo %1!go%2! >> %3!.sql",@delimit,@delimit,@tabname,@echop  
   print "%4!echo %1!declare @retcode int%2! >> %3!.sql",@delimit,@delimit,@tabname,@echop  
   print "%7!echo %1!exec @retcode = sp_gen_subcmp @pds='%8!',@rds='%9!',@pdb='%13!',@rdb='%14!',@ptable='%3!',@visual=%15!,@puser='%10!',@ruser='%11!',@ppwd='%4!',@rpwd='%5!'%2!,@recon='%12!' >> %6!.sql",@delimit,@delimit,@tabname,@ppwd,@rpwd,@tabname,@echop,@pds,@rds,@puser,@ruser,@recon,@pdb,@rdb,@visual
-- 
   print "%4!echo %1!go%2! >> %3!.sql",@delimit,@delimit,@tabname,@echop  
   print "%3!isql -w300 -U%5! -P%4! -S%6! -i %1!.sql -o %2!.tmp",@tabname,@tabname,@echop,@ppwd,@puser,@pds 
   if (@os = "W")  
     begin  
       print "%3!findstr /V /C:""(return status = 0)"" %1!.tmp > %2!.cmp",@tabname,@tabname,@echop  
       print "%2!del %1!.tmp",@tabname,@echop  
    end  
   else  
     begin  
       print "%3!cat %1!.tmp | grep -v ""(return status = 0)"" > %2!.cmp",@tabname,@tabname,@echop  
       print "%2!/usr/bin/rm -y %1!.tmp",@tabname,@echop  
    end  
   if (@pause > 0) print "%1!pause",@echop  
   if (@os = "W")  
       print "%2!subcmp -f %1!.cmp",@tabname,@echop  
   else  
       print "%2!rs_subcmp -f %1!.cmp",@tabname,@echop  
   if (@showcounts = 1)  
     begin  
       print "%3!echo %1!set nocount on%2! > pcount.sql",@delimit,@delimit,@echop  
       print "%3!echo %1!use %4!%2! >> pcount.sql",@delimit,@delimit,@echop,@pdb 
       print "%3!echo %1!go%2! >> pcount.sql",@delimit,@delimit,@echop  
       print "%3!echo %1!exec sp_spaceused %4!%2! >> pcount.sql",@delimit,@delimit,@echop,@tabname  
       print "%3!echo %1!go%2! >> pcount.sql",@delimit,@delimit,@echop  
       print "%3!echo %1!set nocount on%2! > rcount.sql",@delimit,@delimit,@echop  
       print "%3!echo %1!use %4!%2! >> rcount.sql",@delimit,@delimit,@echop,@rdb 
       print "%3!echo %1!go%2! >> rcount.sql",@delimit,@delimit,@echop  
       print "%3!echo %1!exec sp_spaceused %4!%2! >> rcount.sql",@delimit,@delimit,@echop,@tabname  
       print "%3!echo %1!go%2! >> rcount.sql",@delimit,@delimit,@echop  
       print "%3!echo %1!Primary table count:%2!",@delimit,@delimit,@echop  
       print "%1!isql -w300 -U%3! -P%2! -S%4! -i pcount.sql",@echop,@ppwd,@puser,@pds 
       print "%3!echo %1!Replicate table count:%2!",@delimit,@delimit,@echop  
       print "%1!isql -w300 -U%3! -P%2! -S%4! -i rcount.sql",@echop,@rpwd,@ruser,@rds 
    end  
   if (@pause > 0) print "%1!pause",@echop  
   print "" 
end     
  
drop table #table_list  
return 0  
go

SETUSER
go


