use sybsystemprocs
go
if exists (select name from sysobjects where name = "sp_generate_insert_script")
begin
  drop proc sp_generate_insert_script
  print "old version of sp_generate_insert_script dropped"
end
go

create procedure sp_generate_insert_script
                 @tablename_mask varchar(30) = NULL
as
begin
-- +------------------------------------------------------------------+
-- | Stored Procedure:  sp_generate_insert_script                     |
-- | Language:          Sybase Transact SQL (11.5)                    |
-- | Author:            Reinoud van Leeuwen (reinoud@xs4all.nl)       |
-- | Version:           1.2                                           |
-- | Date:              October 13th, 1998                            |
-- | Description:       This stored procedure generates a script      |
-- |                    of select statements that will generate a     |
-- |                    SQL script to fill the tables in the database |
-- |                    with their current content.                   |
-- | Parameters:        IN: @tablename_mask : mask for tablenames     |
-- | History:           1.0 October 3rd 1998                          |
-- |                      first version                               |
-- |                    1.1 October 7th 1998                          |
-- |                      added limited support for text fields; the  |
-- |                      first 252 characters are selected.          |
-- |                    1.2 October 13th 1998                         |
--                        added support for user-defined datatypes    |
-- +------------------------------------------------------------------+

  declare @tablecount      numeric (7,0)
  declare @tablecount_max  numeric (7,0)
  declare @tablename       varchar (30)
  declare @tableid         int
  declare @columncount     numeric (7,0)
  declare @columncount_max numeric (7,0)
  declare @columnname      varchar (30)
  declare @columntype      int
  declare @string          varchar (30)

  set nocount off

  -- take ALL tables when no mask is givven (!)
  if (@tablename_mask is NULL)
  begin
    select @tablename_mask = "%"
  end

  -- create table columninfo now, because it will be used several times

  create table #columninfo
  (num      numeric (7,0) identity,
   name     varchar(30),
   usertype smallint)

  -- create a table with print lines, so it will work too when using ODBC
  -- (ODBC does not like print statements)

  create table #printlines
  (num    numeric (7,0) identity,
   line   varchar (255))

  select name,
         id,
         num  = identity (7)
    into #tablenames
    from sysobjects
   where type in ("U" ,"S")
     and name like @tablename_mask

  -- loop through the table #tablenames

  select @tablecount     = MIN (num),
         @tablecount_max = MAX (num)
    from #tablenames

  while @tablecount <= @tablecount_max
  begin
    select @tablename = name,
           @tableid   = id
      from #tablenames
     where num = @tablecount
    if (@@rowcount <> 0)
    begin
      truncate table #columninfo

      insert into #columninfo (name,usertype)
      select name, type
        from syscolumns C
       where id = @tableid


      insert into #printlines (line) values ("select ""-- generated script "" =")
      insert into #printlines (line) values ("       ""insert into "+@tablename+"""")
      insert into #printlines (line) values ("       + char(10)")
      insert into #printlines (line) values ("       + ""(""")

      select @columncount     = MIN (num),
             @columncount_max = MAX (num)
        from #columninfo
      while @columncount <= @columncount_max
      begin
        select @columnname = name
          from #columninfo
         where num = @columncount
        if (@@rowcount <> 0)
        begin
          if (@columncount < @columncount_max)
          begin
            insert into #printlines (line) values ("       + """+@columnname+",""")
          end
          else
          begin
            insert into #printlines (line) values ("       + """+@columnname+")""")
          end
        end

        select @columncount = @columncount + 1
      end

      insert into #printlines (line) values ("       + char(10),")
      insert into #printlines (line) values ("         ""-- line 0 "" =""values""")
      insert into #printlines (line) values ("       + char(10)")
      insert into #printlines (line) values ("       + ""(""")

      select @columncount     = MIN (num),
             @columncount_max = MAX (num)
        from #columninfo

      while @columncount <= @columncount_max
      begin
        select @columnname = name,
               @columntype = usertype
          from #columninfo
         where num = @columncount

        if (@@rowcount <> 0)
        begin

          if @columntype in (39,47) /* need quotes */
          begin
            insert into #printlines (line) values ("       + substring ('""',1,abs (sign (isnull (char_length ("+@columnname+"), 0))))")
            insert into #printlines (line) values ("       + rtrim (isnull ("+ @columnname+",""NULL"")) " )
            insert into #printlines (line) values ("       + substring ('""',1,abs (sign (isnull (char_length ("+@columnname+"), 0))))")
          end

          else if @columntype = 35 /* TEXT fields cannot be RTRIM-ed and need quotes */
                                   /* convert to VC 252 to leave space for '"",'     */
          begin
            insert into #printlines (line) values ("       + substring ('""',1,abs (sign (isnull (datalength ("+@columnname+"), 0))))")
            insert into #printlines (line) values ("       + rtrim (isnull (convert(varchar(252),"+ @columnname+"),""NULL"")) " )
            insert into #printlines (line) values ("       + substring ('""',1,abs (sign (isnull (datalength ("+@columnname+"), 0))))")
          end
          else if @columntype in (58,61,111) /* datetime fields */
          begin
            insert into #printlines (line) values ("       + substring ('""',1,abs (sign (isnull (char_length (convert(varchar(20),"+@columnname+")), 0))))")
            insert into #printlines (line) values ("       + isnull (convert (varchar(20)," + @columnname + "),""NULL"") ")
            insert into #printlines (line) values ("       + substring ('""',1,abs (sign (isnull (char_length (convert(varchar(20),"+@columnname+")), 0))))")
          end

          else   /* numeric types */
          begin
            insert into #printlines (line) values ("       + rtrim (isnull (convert (char(99),"+@columnname +"), ""NULL""))")
          end

          if ( @columncount < @columncount_max)
          begin
            insert into #printlines (line) values ("       + "",""")
            insert into #printlines (line) values ("       ,")
            select @string = '       "-- line '
                           + rtrim (convert (varchar(3), @columncount))
                           +' " = " "'
            insert into #printlines (line) values (@string)
            insert into #printlines (line) values ("       + char(10)")
          end
        end
        select @columncount = @columncount + 1
      end

    end
    insert into #printlines (line) values ("       + "")""")
    insert into #printlines (line) values ("       + char(10)")
    insert into #printlines (line) values ("       + ""go""")
    insert into #printlines (line) values ("       + char(10)")

    insert into #printlines (line) values ("  from "+@tablename)
    insert into #printlines (line) values ("go")

    select @tablecount = @tablecount + 1
  end

  select "-- generated META script:" = line
    from #printlines
   order by num

end