-- 
-- Permission to use, copy, modify and distribute this code for 
-- NON-commercial purposes and without fee is hereby granted provided
-- that this copyright notice appears in all copies.
-- Sybase shall not be liable for any damages as a result of using,
-- modifying or distributing this code.
--
-- Name : gen_rep_sub.sp
-- Auth.: R.Quakkelaar (Sybase Inc.)
-- Vers.: 1.0
-- Date : Feb. 1998
-- Desc.: Generates script for subscriptions in repl. domain.
--

use sybsystemprocs
go

if exists (select 1 from sysobjects
           where 
                  name = "sp_gen_repsub"
           and    sysstat & 7 = 4 )
begin
   print "Dropping procedure sp_gen_repsub"
   drop proc sp_gen_repsub
end
go

print "Installing procedure sp_gen_repsub"
go

create proc sp_gen_repsub
(
 @servername  varchar(30) = null
,@dbname      varchar(30) = null
,@mode        varchar(30) = null

)
as 

set nocount on

if (@servername = null)
begin
   print "Replicate servername is required ...."
   return -1
end

if (@mode = null) 
or (@mode not in ("create", "define", "activate", "validate", "drop", "check"))
begin
   print "Specify : create | define | activate | validate | drop or check"
   return -1
end

if (@dbname = null)
   select @dbname = db_name()

declare
         @tabid         int
        ,@msg           varchar(255)
        ,@tabname       varchar(30)
      
create table #table_list
(
 id     int
)

insert #table_list 
       select id 
       from sysobjects 
       where type = 'U' 
       and   name not like 'rs_%' 
       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 
          @tabname = object_name(@tabid)

   select @msg = 
                 @mode
                 + space(01)
                 + "subscription"
                 + space(01)
                 + @tabname
                 + "_sub"

   print @msg

   select @msg = 
                 "for"
                 + space(01)
                 + @tabname
   print @msg

   select @msg =
                 "with replicate at" 
                 + space(01)
                 + @servername 
                 + "." 
                 + @dbname

   print @msg

   if (@mode = "drop")
   begin
      select @msg =  "without purge"
      print @msg
   end

   print "go"
end

drop table #table_list

return
go

grant execute on sp_gen_repsub to public
go