USE sybsystemprocs
go
SETUSER  'dbo'
go
print    'Dropping procedure sp_gen_subcmp'
DROP proc sp_gen_subcmp
go
print    'sp_gen_subcmp'
go

create proc sp_gen_subcmp 
( 
 @pds     varchar(30) = null 
,@pdb     varchar(30) = null 
,@ptable  varchar(30) = null 
,@rds     varchar(30) = null 
,@rdb     varchar(30) = null 
,@rtable  varchar(30) = null 
,@puser   varchar(30) = null 
,@ppwd    varchar(30) = null 
,@ruser   varchar(30) = null 
,@rpwd    varchar(30) = null 
,@recon   varchar(30) = null 
,@visual  int         = null 
,@help    int         = null 
) 
 
as 
 
set nocount on 
 
if (isnull(@help,0) <> 0) 
begin 
 print "sp_gen_subcmp - Creates input files for rs_subcmp to compare or reconcile replicated tables" 
    print "              - Generates automatic select statements based on indexes and text/data cols" 
    print "" 
    print "Usage: sp_gen_subcmp @pds [,@pdb] [,@ptable] ,@rds [,@rdb] [,@rtable] [,@puser]" 
    print "                     [,@ppwd] [,@ruser] [,@rpwd] [,@recon]" 
    print "Where:" 
    print " @pds    - Primary Data Server name" 
    print " @pdb    - Primary database name" 
 print "    @ptable - Primary table to use in comparison - if null, all tables in" 
    print "           current database is done. Can be a partial name eg. 'ab_%%'" 
    print " @rds    - Replicate Data Server name" 
    print " @rdb    - Replicate database name - if blank, same as @pdb" 
    print " @rtable - Replicate table to use in compare - if blank, same as @ptable" 
    print " @puser  - Username used to log in to Primary Data Server" 
    print " @ppwd   - Password for user in Primary Data Server" 
    print " @ruser  - Username used to log in to Replicate Data Server" 
    print " @rpwd   - Password for user in Replicate Data Server" 
    print " @recon  - Method for reconcile :" 
    print "          'RECONCILE'       - Reconcile without primary re-verify (default)" 
    print "          'RECONCILE_CHECK' - Reconcile with Primary re-verification" 
    print "          'NONE'            - No reconciliation - check only" 
    print " @visual - If 1 (true) visually print reconciliation info - default is 1" 
    return 0 
end 
 
if (@pds = null) 
begin 
   print "Usage: sp_gen_subcmp @pds [,@pdb] ,@ptable ,@rds [,@rdb] [,@rtable] [,@puser]" 
   print "                     [,@ppwd] [,@ruser] [,@rpwd] [,@recon]" 
   print "Primary servername is required ...." 
   return -1 
end 
if (@rds = null) 
begin 
   print "Usage: sp_gen_subcmp @pds [,@pdb] ,@ptable ,@rds [,@rdb] [,@rtable] [,@puser]" 
   print "                     [,@ppwd] [,@ruser] [,@rpwd] [,@recon]" 
   print "Replicate servername is required ...." 
   return -1 
end 
 
declare 
        @subcmp_name   varchar(30) 
        ,@tabid         int 
        ,@colid         tinyint 
        ,@msg           varchar(255) 
        ,@pmsg           varchar(255) 
  ,@txt           varchar(255) 
        ,@indid     int 
        ,@indflag       int 
        ,@counter       int 
        ,@colname       varchar(30) 
        ,@type          varchar(30) 
        ,@ident         bit 
       ,@reconcile     varchar(30) 
       ,@sel           int 
       ,@pselect1       varchar(255) 
       ,@pselect2       varchar(255) 
       ,@pselect3       varchar(255) 
       ,@pselect4       varchar(255) 
       ,@pselect5       varchar(255) 
       ,@pselect6       varchar(255) 
       ,@pselect7       varchar(255) 
       ,@pselect8       varchar(255) 
       ,@pselect9       varchar(255) 
       ,@pselect10      varchar(255) 
       ,@pselect11      varchar(255) 
       ,@pselect12      varchar(255) 
       ,@pselect13      varchar(255) 
       ,@pselect14      varchar(255) 
       ,@pselect15      varchar(255) 
       ,@pselect16      varchar(255) 
       ,@pselect17      varchar(255) 
       ,@pselect18      varchar(255) 
       ,@pselect19      varchar(255) 
       ,@pselect20      varchar(255) 
       ,@porder1       varchar(255) 
       ,@porder2       varchar(255) 
       ,@pfrom         varchar(255) 
       ,@porderby      varchar(255) 
       ,@indidname     varchar(255) 
       ,@tabidname     varchar(255) 
       ,@maxcol        int 
 
if (upper(@recon) not in ("RECONCILE","RECONCILE_CHECK","NONE")) 
begin 
   print "Usage: sp_gen_subcmp @pds [,@pdb] ,@ptable ,@rds [,@rdb] [,@rtable] [,@puser]" 
   print "                     [,@ppwd] [,@ruser] [,@rpwd] [,@recon]" 
   print "       @recon must be one of : 'RECONCILE', 'RECONCILE_CHECK', 'NONE'" 
   return -1
end 
 
select @visual = isnull(@visual,1) 
 
if (upper(@recon) = "NONE") 
    select @reconcile = "RECONCILE=N" 
else 
    select @reconcile = isnull(@recon,"RECONCILE") + "=Y" 
 
if (@pdb = null) 
   select @pdb = db_name() 
if (@rdb = null) 
   select @rdb = db_name() 
 
create table #tmp_table_list 
( 
 name    varchar(30), 
 id      int, 
 indflag int 
) 
create table #table_list 
( 
 id     int, 
 indflag int 
) 
 
create table #table_def 
( 
 colid  tinyint 
,name   char(30) 
,type   varchar(30) 
,length tinyint  null 
,prec   tinyint  null 
,scale  tinyint  null 
,ident  bit 
) 
 
if (@ptable = null) 
begin 
  insert #tmp_table_list 
       select name,id,indflag=1 
       from sysobjects 
       where type = 'U' 
       and   name not like 'rs_%' 
       and name not like 'spt%' 
       and id in (select distinct id from sysindexes where status = status | 2) 
       UNION 
       select name,id,indflag=0 
       from sysobjects 
     where type = 'U' 
       and   name not like 'rs_%' 
       and name not like 'spt%' 
       and id in (select distinct id from sysindexes where id not in  
             (select distinct id from sysindexes where status = status | 2)) 
end 
else 
begin 
  insert #tmp_table_list 
       select name,id,1 
       from sysobjects 
       where type = 'U' 
       and   name not like 'rs_%' 
       and name not like 'spt%' 
       and id in (select distinct id from sysindexes where status = status | 2) 
       and name like @ptable 
       UNION 
       select name,id,0 
       from sysobjects 
       where type = 'U' 
       and   name not like 'rs_%' 
       and name not like 'spt%' 
       and id in (select distinct id from sysindexes where id not in  
                (select distinct id from sysindexes where status = status | 2)) 
       and name like @ptable 
end 
 
insert #table_list  
    select id, indflag from #tmp_table_list 
    order by name 
 
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 @indflag = indflag from #table_list where id=@tabid 
 
   insert   #table_def 
   select   A.colid, A.name, C.name, A.length, A.prec, A.scale, 
            ident = isnull(convert(bit,(A.status & 0x80)),0) 
   from 
            syscolumns A, 
            systypes B, 
            systypes C 
   where    A.id = @tabid 
   and A.usertype = B.usertype 
   and B.type = C.type 
   group by A.colid 
   having C.usertype = min(C.usertype) 
   and    A.id = @tabid 
   and    A.usertype = B.usertype 
   and B.type = C.type 
   order by A.colid 
 
   select @pselect1="",@pselect2="",@pselect3="",@pselect4="",@pselect5="" 
   select @pselect6="",@pselect7="",@pselect8="",@pselect9="",@pselect10="" 
   select @pselect11="",@pselect12="",@pselect13="",@pselect14="",@pselect15="" 
   select @pselect16="",@pselect17="",@pselect18="",@pselect19="",@pselect20="" 
   select @pfrom="",@porderby="",@porder1="",@porder2="" 
   select 
          @subcmp_name = object_name(@tabid) 
    
   print "# --- Start: %1!.cmp --- #",@subcmp_name 
   print "# Input file for rs_subcmp, Created with sp_gen_subcmp" 
 
   print "#" 
 
   select @msg = "PDS="  
              + @pds 
   print @msg 
 
   select @msg = "RDS=" 
              + @rds 
   print @msg 
 
   select @msg = "PDB="  
              + @pdb 
   print @msg 
    
   select @msg = "RDB=" 
              + @rdb 
   print @msg 
 
   select @msg =  "PTABLE="  
              + @subcmp_name 
   print @msg 
 
   select @msg = "RTABLE=" 
              + isnull(@rtable,@subcmp_name) 
   print @msg 
 
   select @msg="", @txt="", @sel=1 
 
   select @colid = 0 
   select @maxcol = 0 
   while (select min(colid) from #table_def where colid > @colid) != NULL 
   begin 
      select @colid = min(colid), @type = type, @ident = ident 
             from #table_def 
             where colid > @colid 
 
      select @type = type, @ident = ident 
             from #table_def 
             where colid = @colid 
       
      select @msg = rtrim(name) 
             from #table_def 
             where colid = @colid 
      select @pmsg = @msg 
 
      if ((select count(*) from #table_def) > 1 
      and (exists (select * from #table_def where colid>@colid)) or (@txt != "")) 
      begin 
       select @msg = @msg + ','  
        select @maxcol = @maxcol + 1 
       if (@maxcol < 16) 
          select @pmsg = @pmsg + ',' 
      end 
 
       
     if (@type = 'text') 
     or (@type = 'image') 
      begin 
       select @txt = @txt + @msg 
      end 
     else 
     begin 
         if (@sel = 1) 
        begin 
          if (datalength(@pselect1)+datalength(@msg) < 255) 
          begin 
                 select @pselect1 = @pselect1 + @msg 
              if (@maxcol < 17) 
                 select @porder1 = @porder1 + @pmsg 
            end 
         else  
             select @sel = 2 
        end 
         if (@sel = 2) 
        begin 
          if (datalength(@pselect2)+datalength(@msg) < 255) 
             begin 
                 select @pselect2 = @pselect2 + @msg 
              if (@maxcol < 17) 
                 select @porder2 = @porder2 + @pmsg 
            end 
         else  
             select @sel = 3 
        end 
         if (@sel = 3) 
        begin 
          if (datalength(@pselect3)+datalength(@msg) < 255) 
             select @pselect3 = @pselect3 + @msg 
         else  
             select @sel = 4 
        end 
         if (@sel = 4) 
        begin 
          if (datalength(@pselect4)+datalength(@msg) < 255) 
             select @pselect4 = @pselect4 + @msg 
         else  
             select @sel = 5 
        end 
         if (@sel = 5) 
        begin 
          if (datalength(@pselect5)+datalength(@msg) < 255) 
             select @pselect5 = @pselect5 + @msg 
         else  
             select @sel = 6 
        end 
         if (@sel = 6) 
        begin 
          if (datalength(@pselect6)+datalength(@msg) < 255) 
             select @pselect6 = @pselect6 + @msg 
         else  
             select @sel = 7 
        end 
         if (@sel = 7) 
        begin 
          if (datalength(@pselect7)+datalength(@msg) < 255) 
             select @pselect7 = @pselect7 + @msg 
         else  
             select @sel = 8 
        end 
         if (@sel = 8) 
        begin 
          if (datalength(@pselect8)+datalength(@msg) < 255) 
             select @pselect8 = @pselect8 + @msg 
         else  
             select @sel = 9 
        end 
         if (@sel = 9) 
        begin 
          if (datalength(@pselect9)+datalength(@msg) < 255) 
             select @pselect9 = @pselect9 + @msg 
         else  
         select @sel = 10 
        end 
         if (@sel = 10) 
        begin 
          if (datalength(@pselect10)+datalength(@msg) < 255) 
             select @pselect10 = @pselect10 + @msg 
         else  
            select @sel = 11  
        end 
         if (@sel = 11) 
      begin 
          if (datalength(@pselect11)+datalength(@msg) < 255) 
             select @pselect11 = @pselect11 + @msg 
         else  
             select @sel = 12 
        end 
         if (@sel = 12) 
        begin 
          if (datalength(@pselect12)+datalength(@msg) < 255) 
             select @pselect12 = @pselect12 + @msg 
         else  
             select @sel = 13 
        end 
         if (@sel = 13) 
        begin 
          if (datalength(@pselect13)+datalength(@msg) < 255) 
             select @pselect13 = @pselect13 + @msg 
         else  
             select @sel = 14 
        end 
         if (@sel = 14) 
        begin 
          if (datalength(@pselect14)+datalength(@msg) < 255) 
             select @pselect14 = @pselect14 + @msg 
         else  
             select @sel = 15 
        end 
         if (@sel = 15) 
        begin 
          if (datalength(@pselect15)+datalength(@msg) < 255) 
             select @pselect15 = @pselect15 + @msg 
         else  
             select @sel = 16 
        end 
         if (@sel = 16) 
        begin 
          if (datalength(@pselect16)+datalength(@msg) < 255) 
             select @pselect16 = @pselect16 + @msg 
         else  
             select @sel = 17 
        end 
         if (@sel = 17) 
        begin 
          if (datalength(@pselect17)+datalength(@msg) < 255) 
             select @pselect17 = @pselect17 + @msg 
         else  
             select @sel = 18 
        end 
         if (@sel = 18) 
        begin 
          if (datalength(@pselect18)+datalength(@msg) < 255) 
             select @pselect18 = @pselect18 + @msg 
         else  
             select @sel = 19 
        end 
         if (@sel = 19) 
        begin 
          if (datalength(@pselect19)+datalength(@msg) < 255) 
             select @pselect19 = @pselect19 + @msg 
         else  
             select @sel = 20 
        end 
         if (@sel = 20) 
        begin 
          if (datalength(@pselect20)+datalength(@msg) < 255) 
             select @pselect20 = @pselect20 + @msg 
         else  
            print "PSELECT > 5100 chars: %1!", @msg 
        end 
     end 
   end 
   select @porderby = "order by " 
 
   select @indid = min(indid) 
   from sysindexes 
   where id = @tabid 
   and indid > 0 
   and (status & 2) = 2 
 
   if (@indid <> NULL) 
   begin 
      select @counter = 1 
      while @counter <= 16 
      begin 
         select @colname =  index_col(object_name(@tabid), @indid, @counter) 
         if (@colname is NULL) or (@colname = '') 
              break 
         if (@counter > 1) 
            select @porderby = @porderby + ","
        select @porderby = @porderby + rtrim(@colname) 
         select @counter = @counter + 1 
      end 
   end 
   if (@indflag = 0) 
   begin 
     select @porderby = @porderby +" \" 
   end 
 
   print "PSELECT= select \" 
   if (@pselect1 != "")  
     print "%1! \", @pselect1 
   if (@pselect2 != "")  
     print "%1! \", @pselect2 
   if (@pselect3 != "")  
     print "%1! \", @pselect3 
   if (@pselect4 != "")  
     print "%1! \", @pselect4 
   if (@pselect5 != "")  
     print "%1! \", @pselect5 
   if (@pselect6 != "")  
     print "%1! \", @pselect6 
   if (@pselect7 != "")  
     print "%1! \", @pselect7 
   if (@pselect8 != "")  
     print "%1! \", @pselect8 
   if (@pselect9 != "")  
     print "%1! \", @pselect9 
   if (@pselect10 != "")  
     print "%1! \", @pselect10 
   if (@pselect11 != "")  
     print "%1! \", @pselect11 
   if (@pselect12 != "")  
     print "%1! \", @pselect12 
   if (@pselect13 != "")  
     print "%1! \", @pselect13 
   if (@pselect14 != "")  
     print "%1! \", @pselect14 
   if (@pselect15 != "")  
     print "%1! \", @pselect15 
   if (@pselect16 != "")  
     print "%1! \", @pselect16 
   if (@pselect17 != "")  
     print "%1! \", @pselect17 
   if (@pselect18 != "")  
     print "%1! \", @pselect18 
   if (@pselect19 != "")  
     print "%1! \", @pselect19 
   if (@pselect20 != "")  
     print "%1! \", @pselect20 
   if (@txt != "") 
   begin 
      if (substring(@txt,datalength(@txt),1) = ",") 
        select @txt = substring(@txt,1,datalength(@txt) -1) + " \" 
      if (substring(@txt,datalength(@txt),1) <> "\") 
        select @txt = @txt + " \" 
      print "%1!",@txt 
   end 
   print "from %1! \", @subcmp_name 
   print @porderby 
 
   if (@indflag = 0) 
   begin 
     if (@porder1 <> "") 
       begin 
         if (@porder2 <> "") 
            print "%1! \",@porder1 
         else
            begin
              if (substring(@porder1,datalength(@porder1),1) = ",") 
              select @porder1 = substring(@porder1,1,datalength(@porder1) -1)
              print @porder1 
     end
       end 
     if (@porder2 <> "") 
      begin
        if (substring(@porder2,datalength(@porder2),1) = ",") 
        select @porder2 = substring(@porder2,1,datalength(@porder2) -1)
        print @porder2 
      end
   end 
 
   print "RSELECT= select \" 
   if (@pselect1 != "")  
     print "%1! \", @pselect1 
   if (@pselect2 != "")  
     print "%1! \", @pselect2 
   if (@pselect3 != "")  
     print "%1! \", @pselect3 
   if (@pselect4 != "")  
     print "%1! \", @pselect4 
   if (@pselect5 != "")  
     print "%1! \", @pselect5 
   if (@pselect6 != "")  
     print "%1! \", @pselect6 
   if (@pselect7 != "")  
     print "%1! \", @pselect7 
   if (@pselect8 != "")  
     print "%1! \", @pselect8 
   if (@pselect9 != "")  
     print "%1! \", @pselect9 
   if (@pselect10 != "")  
     print "%1! \", @pselect10 
   if (@pselect11 != "")  
     print "%1! \", @pselect11 
   if (@pselect12 != "")  
     print "%1! \", @pselect12 
   if (@pselect13 != "")  
     print "%1! \", @pselect13 
   if (@pselect14 != "")  
     print "%1! \", @pselect14 
   if (@pselect15 != "")  
     print "%1! \", @pselect15 
   if (@pselect16 != "")  
     print "%1! \", @pselect16 
   if (@pselect17 != "")  
     print "%1! \", @pselect17 
   if (@pselect18 != "")  
     print "%1! \", @pselect18 
   if (@pselect19 != "")  
     print "%1! \", @pselect19 
   if (@pselect20 != "")  
     print "%1! \", @pselect20 
   if (@txt != "") 
   begin 
      if (substring(@txt,datalength(@txt),1) = ",") 
        select @txt = substring(@txt,1,datalength(@txt) -1) + " \" 
      if (substring(@txt,datalength(@txt),1) <> "\") 
        select @txt = @txt + " \" 
      print @txt 
   end 
   select @msg=isnull(@rtable,@subcmp_name) 
   print "from %1! \", @msg 
   print @porderby 
 
   if (@indflag = 0) 
   begin 
     if (@porder1 <> "") 
       begin 
         if (@porder2 <> "") 
            print "%1! \",@porder1 
         else 
            print @porder1 
     end 
     if (@porder2 <> "") print @porder2 
   end 
   
   select @indid = min(indid) 
   from sysindexes 
   where id = @tabid 
   and indid > 0 
   and (status & 2) = 2 
 
   if (@indid <> NULL) 
   begin 
      select @counter = 1 
      while @counter <= 16 
      begin 
         select @colname = index_col(object_name(@tabid), @indid, @counter) 
         if (@colname is NULL) or (@colname = '') 
            break 
         select @msg = "KEY=" + rtrim(@colname) 
         print @msg 
         select @counter = @counter + 1 
      end 
   end 
 
   if (@indflag = 0) 
   begin 
     select @colid = 0 
    select @maxcol = 0 
     while (select min(colid) from #table_def where colid > @colid) != NULL 
     begin 
       select @colid = min(colid), @type = type, @ident = ident 
            from #table_def 
            where colid > @colid 
        
       select @type = type, @ident = ident 
            from #table_def 
            where colid = @colid 
   
      if @maxcol < 16 
      begin 
         if (@type <> 'text') 
         and (@type <> 'image') 
         begin 
          select @maxcol = @maxcol + 1 
           select @msg = "KEY="+rtrim(col_name(@tabid,@colid)) 
           print @msg 
 end 
       end 
     end 
   end 
    
   select @colid = 0 
   while (select min(colid) from #table_def where colid > @colid) != NULL 
   begin 
     select @colid = min(colid), @type = type, @ident = ident 
          from #table_def 
   where colid > @colid 
        
     select @type = type, @ident = ident 
          from #table_def 
          where colid = @colid 
 
     if (@ident = 1) 
     begin 
        select @maxcol = @maxcol + 1 
        select @msg = "IDENTITY="+rtrim(col_name(@tabid,@colid)) 
        print @msg 
     end 
   end 
 
   truncate table #table_def 
   if (@visual = 1) 
     select @msg = "Y" 
   else 
     select @msg = "N" 
   print "PUSER=%1!",@puser 
   print "RUSER=%1!",@ruser 
   print "PPWD=%1!",@ppwd 
   print "RPWD=%1!",@rpwd 
   print @reconcile 
   print "VISUAL=%1!",@msg 
   print "NUM_TRIES=3" 
   print "WAIT=10" 
   print "#" 
   print "# --- End: %1!.cfg --- # ", @subcmp_name 
 end 
 
drop table #table_def 
drop table #table_list 
drop table #tmp_table_list 
 
 
return 
go

SETUSER
go
