-- 
-- 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_alter_def.sp
-- Auth.: R.Quakkelaar (Sybase Inc.)
-- Vers.: 1.0
-- Date : Feb. 1998
-- Desc.: Generates alter replication definition script for all user tables in a
--        specified database with text/image columns.
--

use sybsystemprocs
go

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

print "Installing procedure sp_gen_alterdef"
go

create proc sp_gen_alterdef
(
 @dbname      varchar(30) = null
)
as 

set nocount on

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

declare
         @rep_def_name  varchar(30)
        ,@tabid         int
        ,@colid         tinyint
        ,@msg           varchar(255)
        ,@indid	        int
        ,@counter       int
        ,@colname       varchar(30)
        ,@set           int
      
create table #table_list
(
 id     int
)

create table #table_def
(
 colid 	tinyint
,name   char(30)
,type   varchar(30)
,length tinyint
)

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

   /* Get the name, type, and length of the columns for this table. */
   insert   #table_def
   select   A.colid, A.name, C.name, A.length
   from     
            syscolumns A, 
            systypes B, 
            systypes C
   where    A.id = @tabid
   and A.usertype = B.usertype
   and B.type = C.type
   and ( C.usertype <= 25
         or C.usertype = 80 )
   group by A.colid
   having C.usertype = min(C.usertype)
   and    A.id = @tabid
   and    A.usertype = B.usertype
   and B.type = C.type
   and ( C.usertype <= 25
         or C.usertype = 80 )
   order by A.colid

   select 
          @rep_def_name = object_name(@tabid)

   select @msg = "alter replication definition"
                 + space(01)
                 + @rep_def_name

   select @colid = 0
   select @set=0
   while (select min(colid) from #table_def where colid > @colid) != NULL
   begin
      select @colid = min(colid) 
             from #table_def 
             where colid > @colid

      if exists (select 1 
                 from #table_def
                 where colid = @colid
                 and type in ('text', 'image'))
      begin
         if (@set = 0)
         begin
            print @msg
            select @msg = "replicate_if_changed"
            print @msg
            select @set = 1
            select @msg = ""
         end
      
         select @msg = @msg 
                       + name 
         from   #table_def
         where  colid = @colid

         if (select count(*) from #table_def) > 1
         and exists (select * from #table_def where colid>@colid
                     and type in ("text", "image"))
         begin
            select @msg = @msg + ', '
         end
      end
   end

   if (@set = 1)
   begin
      print @msg
      print 'go'
   end

   truncate table #table_def
end

drop table #table_def
drop table #table_list

return
go

grant execute on sp_gen_alterdef to public
go