use sybsystemprocs
go

IF OBJECT_ID('dbo.sp_gen_repdef') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.sp_gen_repdef
    IF OBJECT_ID('dbo.sp_gen_repdef') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_gen_repdef >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp_gen_repdef >>>'
END
go

create proc dbo.sp_gen_repdef
( 
 @servername   varchar(30)  = null 
,@dbname       varchar(30)  = null 
,@tablename    varchar(30)  = "%" 
,@prefix       varchar(6)   = null 
,@suffix       varchar(6)   = null 
,@alterimg     varchar(30)  = null 
,@minimal      int          = 0 
,@shownulls    int          = 1
,@shownotnulls int          = 0
,@ws           int          = 0
,@wsrepcol     int          = 0
,@removecol    varchar(255) = null
,@distribution varchar(10)  = null
,@help         int          = null
,@debug        int          = 0
) 
as

-- 
-- 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 : rep_defs.sp
-- Auth.: R.Quakkelaar (Sybase Inc.)
-- Vers.: 1.0
-- Date : Feb. 1998
-- Desc.: Generates create replication definition script for all user tables in a
--        specified database
--        The primary key() clause is generated based on the first index on the table
--        with the unique property.
--
-- Note :
--        By default, identity columns are published. Minimal columns option is no
--        longer used as default - As of Vers. 4, replicate all columns is default.
--
-- Vers.: 2.0
-- Date : Nov. 2000
-- Mod. : C.Cornelius (Sybase SA)
-- Desc.: Commented out code to allow underlying datatypes to be extracted for
--        user-defined types.
--        Allowed addition of prefix and / or suffix to RepDef name.
--
-- Vers.: 3.0
-- Date : 23 Dec. 2002
-- Mod. : C.Cornelius (Sybase SA)
-- Desc.: Added functionality to allow inclusion of "null / not null" syntax in repdef. 
--        This is to acommodate RepServer 12.1 and 12.5 which is a bit more picky about
--        syntax, and does not seem to like it.
--        Added functionality to use all non-text / non-image columns in "primary key" clause,
--        if no key exists.
--        Added functionality to enclose RepDef and column names in quotes, to allow for reserved
--        words to be used as RepDef and column names.
--
-- Vers.: 4.0
-- Date : Feb 2003
-- Mod. : C.Cornelius (Sybase SA)
-- Desc.: Special adaptions to suit DCS environment for custom repdef generation 
--        Altered @shownulls / @shownotnulls to seperate null / not null generation option in repdefs.
--        Added @removecol to allow omission of column names from repdefs.
--        
--
-- Vers.: 5.0
-- Date : 29 Oct 2003
-- Mod. : C.Cornelius (Sybase SA)
-- Desc.: Special adaptions to suit Warm Standby environment for repdef generation
--        Altered @prefix and @suffix to accept 6 characters
--        Added @ws to generate "send standby" clause 
--        Added @wsrepcol for "send standby all columns"/"send standby replication definition columns" clause.
--        
-- Vers.: 5.1
-- Date : 04 Feb 2004
-- Mod. : C.Cornelius (Sybase SA)
-- Desc.: Added "set rowcount 0" after some funnies with repdef generation in a session where rowcount was set !!
--
--
-- Vers.: 5.2
-- Date : 04 Mar 2004
-- Mod. : C.Cornelius (Sybase SA)
-- Desc.: Altered null / not null syntax to only generate null / not null syntax for text & image columns like it was intended
--
--
-- Vers.: 5.3
-- Date : 11 May 2004
-- Mod. : C.Cornelius (Sybase SA)
-- Desc.: Fixed missing else clause causing dupes for numeric & decimals in repdef
--        Fixed bug on "primary key" clause that left a remaining comma if rest of columns were text and image
--
--
--
set nocount on
set rowcount 0 
 
if (@help <> null) 
begin 
   print "sp_gen_repdef - Used to generate replication definitions for RepServer from a database" 
   print "" 
   print "Usage: sp_gen_repdef @servername [,@dbname] ,[@tablename] [,@prefix] [,@suffix] [,@alterimg] [,@minimal]"
   print "                     [,@shownulls] [,@shownotnulls] [,@ws] [,@wsrepcol] [,@removecol]"
   print "Where:" 
   print "  @servername   - Name of primary data server" 
   print "  @dbname       - Name of primary database - current database if null" 
   print "  @tablename    - Generate for specific table or 'tbl%%' for 'like' tables" 
   print "                  All tables in database if null" 
   print "  @prefix       - 6 Character prefix to add before repdef name" 
   print "  @suffix       - 6 Character suffix to add after repdef name" 
   print "  @alterimg     - Generate 'alter repdef' to set 'always_replicate' or" 
   print "                  or 'replicate_if_changed' for text / image cols" 
   print "                  Valid values are 'always_replicate' or 'replicate_if_changed'" 
   print "  @minimal      - Set to 1 (true) to use 'replicate minimal columns'" 
   print "                  Default is 0 (false) to use 'replicate all columns'" 
   print "  @shownulls    - Include 'null' syntax in repdef if 1 (true)"
   print "                  Default is 1 - use 'null' syntax"
   print "  @shownotnulls - Include 'not null' syntax in repdef if 1 (true)"
   print "                  Default is 0 - do NOT use 'null / not null' syntax"
   print "  @ws           - Add 'send standby . . .' clause for warm standby repdefs if 1"
   print "                  Default is 0 - do NOT add 'send standby . . .' clause."
   print "  @wsrepcol     - Use 'send standby replication definition columns' for warm standby repdefs if 1"
   print "                  Use 'send standby all columns' for warm standby repdef if 0"
   print "                  Default is 0 - Use 'send standby all columns' clause with @ws=1."
   print "  @removecol    - Comma-separated list of column names to remove from repdef"
   print "  @distribution - If used, special consideration will be used for repdef names, such as"
   print "                  the addition of a distribution code to the end of the repdef name."
   print "                  If the repdef name exceeds 30 characters, trim it down to below 30, and"
   print "                  use a suffix of a-z for duplicate names eg Table_7205 Table_7205a"
   return 0 
end 
 
if (@servername = null) 
begin 
   print "Usage: sp_gen_repdef @servername [,@dbname] ,[@tablename] [,@prefix] [,@suffix] [,@alterimg] [,@minimal]"
   print "                     [,@shownulls] [,@shownotnulls] [,@ws] [,@wsrepcol] [,@removecol]"
   print "Primary servername is required ...."
   print "Execute ""sp_gen_repdef @help=1"" to show complete syntax"
   return -1 
end 
 
if (@dbname = null) 
   select @dbname = db_name() 
 
if @alterimg not in (null, "always_replicate","replicate_if_changed") 
begin 
   print "Usage: sp_gen_repdef @servername [,@dbname] ,[@tablename] [,@prefix] [,@suffix] [,@alterimg] [,@minimal]"
   print "                     [,@shownulls] [,@shownotnulls] [,@ws] [,@wsrepcol] [,@removecol]"
   print "       @alterimg should be one of : always_replicate or replicate_if_changed" 
   print "Execute ""sp_gen_repdef @help=1"" to show complete syntax"
   return -1 
end 
 
declare 
         @rep_def_name  varchar(50)
        ,@tabname       varchar(30) 
        ,@tabid         int 
        ,@colid         smallint 
        ,@msg           varchar(255) 
        ,@indid         int 
        ,@indflag       int 
        ,@counter       int 
        ,@colname       varchar(30) 
        ,@type          varchar(30) 
        ,@ident         bit 
        ,@nulls         int
        ,@notnulls      bit
        ,@charlist      varchar(52)
 
 
create table #tmp_table_list 
( 
 name    varchar(30), 
 id      int, 
 indflag int,
 rdname  varchar(30) null
 ) 
 
create table #table_list 
( 
 id      int, 
 indflag int 
) 
 
create table #table_def 
( 
 colid  smallint 
,name   char(30) 
,type   varchar(30) 
,length smallint  null 
,prec   smallint  null 
,scale  smallint  null 
,ident  bit 
,nulls  int
)

create table #removecol
(
 name varchar(30)
)

select @distribution = isnull(@distribution,'')
if datalength(rtrim(@distribution)) > 0 select @distribution = rtrim(@distribution) + "_"
select @tablename = isnull(@tablename,"%")

insert #tmp_table_list 
     select name,id, 1,null 
     from sysobjects 
     where type = 'U' 
     and name not like 'rs_%' 
     and name not like 'spt%' 
     and name not like 'rmt_ha_%'
     and id in (select distinct id from sysindexes where status = status | 2) 
     and name like @tablename 
insert #tmp_table_list 
     select name,id, 0,null 
     from sysobjects 
     where type = 'U' 
     and name not like 'rs_%' 
     and name not like 'spt%' 
     and name not like 'rmt_ha_%'
     and id in (select distinct id from sysindexes where id not in  
               (select distinct id from sysindexes where status = status | 2)) 
     and name like @tablename 
 
if (@alterimg <> null) 
   delete from #tmp_table_list 
   where id not in ( 
       select distinct c.id from  
       syscolumns c, systypes t 
       where c.type = t.type 
       and t.name in ("text","image")) 
 
insert into #table_list  
select id, indflag 
from #tmp_table_list 
order by name 

if (@debug = 1)
begin
    select '#table_list', #table_list.id, #table_list.indflag from #table_list
end

select @tabid = 0 
 
while (select min(id) from #tmp_table_list where id > @tabid) != NULL 
begin 
   select @tabid = min(id) 
   from #tmp_table_list where id > @tabid 

	select @rep_def_name = rtrim(@distribution) + rtrim(name) from #tmp_table_list where @tabid = id 

	if datalength(@rep_def_name) > 30
	begin
		select @rep_def_name = substring(@rep_def_name,1,30)
	end
	select @charlist = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
	while exists(select rdname from #tmp_table_list where rdname = @rep_def_name)
	begin
		select @rep_def_name = substring(@rep_def_name,1,28) + "_" + substring(@charlist,1,1)
		select @charlist = substring(@charlist,2,datalength(@charlist) -1)
	end
	update #tmp_table_list set rdname = @rep_def_name where id = @tabid
end

if @removecol is not null
begin
  while charindex(",",@removecol) > 0
  begin
    select @charlist = substring(@removecol,1,charindex(",",@removecol)-1)
    if not exists (select name from #removecol where name = @charlist)
      insert into #removecol values (@charlist)
    select @removecol = substring(@removecol,charindex(",",@removecol)+1,datalength(@removecol)-charindex(",",@removecol))
  end
  if datalength(@removecol) > 0 
  begin
    if not exists (select name from #removecol where name = @removecol)
      insert into #removecol values (@removecol)
  end
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 @indflag = indflag from #table_list where @tabid = id 
 
   insert   #table_def 
   select   A.colid, A.name, C.name, A.length, A.prec, A.scale, 
            ident = isnull(convert(bit,(A.status & 0x80)),0), 
            nulls = isnull(convert(bit,((A.status & 0x8)/8)),0) 
   from 
            syscolumns A, 
            systypes B, 
            systypes C 
   where    A.id = @tabid 
   and A.usertype = B.usertype 
   and B.type = C.type 
   and A.name not in (select name from #removecol)
   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 

if (@debug = 1)
begin
    select '#table_def', #table_def.colid, #table_def.name, #table_def.type, #table_def.length, #table_def.prec, #table_def.scale, #table_def.ident, #table_def.nulls from #table_def
end

   select @rep_def_name = rdname, @tabname = name from #tmp_table_list where id = @tabid 

   if ( @indflag = 1 )
   begin
if (@debug = 1)
begin
    print '@indflag=1'
end        
     select @indid = min(indid) 
     from sysindexes 
     where id = @tabid 
     and indid > 0 
     and (status & 2) = 2 
     if @indid is not null
     begin
       select @indflag = 0, @counter = 1 
         while (@counter <= 16) and (@indflag = 0)
         begin
           if index_col(object_name(@tabid), @indid, @counter) not in (select name from #removecol)
             begin
               select @indflag = 1
             end
           select @counter = @counter + 1
         end
     end
   end

   if (@indflag = 0)
   begin
if (@debug = 1)
begin
    print '@indflag=0'
end      select @indflag = indflag from #table_list where @tabid = id
     if @indflag = 1
     begin
       print "-- Warning: no index columns remain for table ""%1!""", @tabname
       select @indflag = 0
     end
   end
   if (select count(*) from #table_def) < 1
   begin
     print "-- Warning: no columns left for table ""%1!"" - RepDef ""%2!"" not created", @tabname, @rep_def_name 
     continue
   end

   if (@indflag = 0) 
   begin
      print "-- Warning: no unique index for ""%1!""",@tabname
      print "-- Check RepDef ""%1!"" as replicate rows may be overwritten", @rep_def_name 
   end
 
   select @charlist = @prefix + @rep_def_name + @suffix
   if datalength(isnull(@charlist,"")) > 30 
   begin 
    select @msg = "-- Warning: RepDef name """+rtrim(isnull(@prefix,""))+@rep_def_name+rtrim(isnull(@suffix,""))+""" longer than 30 characters !" 
    print @msg 
   end 
 
   if (@alterimg = null) 
      select @msg = "create" 
   else 
      select @msg = "alter" 
    
   select @msg = @msg 
                 + " replication definition" 
                 + space(01) 
                 + '"'
                 + rtrim(@prefix)
                 + rtrim(@rep_def_name) 
                 + rtrim(@suffix) 
                 + '"'
   print @msg 
 
   if (@alterimg = null)  
   begin  
   select @msg = "with primary at " + @servername + "." + @dbname 
   print @msg 
   select @msg = "with all tables named" 
   select @msg = @msg 
                 + space(01) 
                 + "'" 
                 + rtrim(@tabname)
                 + "'" 
 
   print @msg 
   print "(" 
 
   select @colid = 0 
     while (select min(colid) from #table_def where colid > @colid) != NULL 
     begin 
        select @colid = min(colid)
               from #table_def 
               where colid > @colid 
 
        select @type = type, @ident = ident, @nulls = nulls 
               from #table_def 
               where colid = @colid 
 
        select @msg = "" 
        
        if (@alterimg = null) 
 
        if (@type = 'char') 
        or (@type = 'varchar') 
        or (@type = 'binary') 
        or (@type = 'varbinary') 
        begin 
if (@debug = 1)
begin
    print '@type = char/varchar/binary/varbinary'
end        
           select @msg = 
                          space(02) 
                        + '"' + rtrim(name) + '"' 
                        + space(01) 
                        + type 
             + '(' 
                        + rtrim(convert(varchar(5), length)) + ')' 
                      + @msg 
           from   #table_def 
           where  colid = @colid 
        end 
        else 
        begin 
           if  (@ident = 0) 
           begin 
              if (@type = 'numeric') 
              or (@type = 'decimal') 
              begin 
if (@debug = 1)
begin
    print '@type = numeric/decimal'
end        
-- 
-- Prec en scale for numeric & decimal columns is not needed 
-- for the Rep Def. 
-- 
                 select @msg = 
                                 space(02) 
                               + '"' + rtrim(name) + '"' 
                               + space(01) 
                               + type 
                               + @msg 
                               -- + "(" 
                               -- + convert(varchar(3),prec) 
                               -- + "," 
                               -- + convert(varchar(3),scale) 
                               -- + ")" 
                 from   #table_def 
                  where  colid = @colid 
              end 
              else
              begin 
                if (@type = 'text') 
                or (@type = 'image') 
                begin 
if (@debug = 1)
begin
   print '@type = text/image'
end        
                   select @msg = 
                                   space(02) 
                                 + '"' + rtrim(name) + '"' 
                                 + space(01) 
                                 + type 
                                 + @msg 
                  from   #table_def 
                    where  colid = @colid 
                  if (isnull(@nulls,2) in (0,1))
                  begin
                    if (@nulls = 1) and (@shownulls = 1) select @msg = @msg + " null" 
                    if (@nulls = 0) and (@shownotnulls = 1 ) select @msg = @msg + " not null" 
                  end
                end
                else 
                begin 
                   select @msg = space(02) 
                                 + '"' + rtrim(name) + '"' 
                                 + space(01) 
                                 + type 
                                 + @msg 
                    from   #table_def 
                    where colid = @colid 
                end
             end
           end
           else
           begin 
             select @msg = space(02) 
                           + '"' + rtrim(name) + '"' 
                           + space(01) 
                           + "identity" 
             from   #table_def 
             where  colid = @colid 
           end 
        end 
 
        if (select count(*) from #table_def) > 1 
        and exists (select * from #table_def where colid>@colid) 
      begin 
           select @msg = @msg + ',' 
        end 
 
        print @msg 
     end 
     print ')' 
 
     select @msg = "" 
 
   if (@indflag = 0) 
   begin 
if (@debug = 1)
begin
    print '@indflag=0'
end
     print "primary key (" 
     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 (@type = 'text') 
         or (@type = 'image') 
         begin 
           select @msg = "-- " 
                         + space(02) 
                         + '"' + rtrim(name) + '"' 
           from   #table_def 
           where  colid = @colid 
         end 
         else 
         begin 
           select @msg = space(02) 
                         + '"' 
                         + rtrim(name) + '"' 
           from   #table_def 
           where  colid = @colid 
        end 
        if (select count(*) from #table_def) > 1 
-- New mod
        and exists (select * from #table_def where colid>@colid and type not in ('text','image')) 
        begin 
           select @msg = @msg + ',' 
        end 
       print @msg 
     end 
    print ")" 
   end 
   else 
   begin 
if (@debug = 1)
begin
    print '@indflag=1'
end
     select @indid = min(indid) 
     from sysindexes 
     where id = @tabid 
     and indid > 0 
     and (status & 2) = 2 
 
     if (@indid <> NULL) 
     begin 
if (@debug = 1)
begin
    print '@indflag<>NULL'
end 
        select @counter = 1 
        while @counter <= 16 
        begin 
           if index_col(object_name(@tabid), @indid, @counter)  in (select name from #removecol)
           begin 
             select @counter = @counter + 1 
             continue
           end
           else
             select @colname = '"' + index_col(object_name(@tabid), @indid, @counter) + '"' 
           if (@colname is NULL) or (@colname = '""') 
              break 
           if (@counter > 1) 
              select @msg = @msg + ", " 
           select @msg = @msg + rtrim(@colname) 
           select @counter = @counter + 1 
        end 
     end 
     print 'primary key (%1!)', @msg 
   end 
 
   if (@ws > 0)
   begin
if (@debug = 1)
begin
    select '@ws>0'
end
   	 if (@wsrepcol = 0)
	   select @msg = 'all columns'
     else
	   select @msg = 'replication definition columns'
     print 'send standby %1!',@msg
   end
   
   if (@minimal > 0)  
     select @msg = "minimal" 
   else  
     select @msg = "all" 
   print 'replicate %1! columns',@msg 
   end 
   else  
   begin 
     print @alterimg 
     select @colid = 0 
     select @counter = 0 
     while (select min(colid) from #table_def where colid > @colid) != NULL 
     begin 
        select @colid = min(colid), @type = type, @ident = ident, @nulls = nulls 
               from #table_def 
               where colid > @colid 
        select @type = type, @ident = ident 
               from #table_def 
               where colid = @colid 
 
  select @msg = "" 
        if (@type = 'image') 
        or (@type = 'text') 
        begin 
if (@debug = 1)
begin
    print '@type = image / text'
end        
           select @counter = @counter + 1 
           if (@counter > 1) 
            select @msg = ", " 
           select @msg = @msg 
                       + '"' + rtrim(name) + '"' 
                from   #table_def 
                where  colid = @colid 
               if (isnull(@nulls,2) in (0,1))
               begin
                  if (@nulls = 1) and (@shownulls = 1) select @msg = @msg + " null" 
                  if (@nulls = 0) and (@shownotnulls = 1 ) select @msg = @msg + " not null" 
               end
           print @msg 
         end 
       end 
     end 
 
   print 'go' 
   print ''
 
   truncate table #table_def 
end 

the_end: 

drop table #removecol
drop table #tmp_table_list 
drop table #table_def 
drop table #table_list 

return 


