dump tran sybsystemprocs with truncate_only
go

use sybsystemprocs
go

if exists (select * from sysobjects where name = "sp_genddl" and type = 'P')
   drop proc sp_genddl
go

create procedure sp_genddl
  ( @objname varchar(30) = null )
as

declare @alwdup varchar(68),
        @buffer varchar(255),
        @count int,
        @count2 int,
        @dup varchar(68),
        @duprow varchar(68),
        @i2 int,
        @indid int,
        @last int,
        @max2 int,
        @msg varchar(250),
        @name varchar(25),
        @objid int,
        @segment varchar(68),
        @thatkey varchar(30),
        @type varchar(68),
        @uname varchar(20),
        @uname2 varchar(20),
        @unique varchar(68),
        @with varchar(68),
        @year char(4)

set nocount on

if ( @objname = NULL )
  begin
    return (0)
  end

/*
**  Make sure the @objname is local to the current database.
*/
if ( @objname like "%.%.%" and
     substring(@objname, 1, charindex(".", @objname) - 1) != db_name() )
  begin
    /* 17460, "Object must be in the current database." */
    exec sp_getmessage 17460, @msg out
    print @msg
    return  (1)
  end

/*
**  Now check to see if the @objname is in sysobjects.  It has to be either
**  in sysobjects or systypes.
*/
select @objid = id
  from sysobjects
 where id = object_id(@objname)

/*
**  It wasn't in sysobjects so we'll check in systypes.
*/
if ( @objid = NULL )
  begin
    print "Object not in this database"
    return (0)
  end


select @count = 0,
       @year=convert(char(4),datepart(year,getdate()))

/* Decide whether we need to SETUSER to the owner of this table */
select @uname = user_name(uid) from sysobjects where name = @objname
if ( @uname != @uname2 )
  begin
    select @buffer = "setuser '" + user_name(uid) + "'" from sysobjects
     where name = @objname
    print @buffer
    print "go"
  end
select @uname2 = @uname

select
@buffer='/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~'
print @buffer
select @buffer=space(14)+'Software Systems Development Corporation'
print @buffer
select @buffer=space(16)+'Copyright '+@year+' - All Rights Reserved'
print @buffer
select
@buffer='-----------------------------------------------------------------------
-'
print @buffer
select @buffer=space(7)+'Table Name: '+@objname
print @buffer
select @buffer=space(5)+'Date Created: '+convert(char(12),getdate(),107)
print @buffer
select
@buffer='~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*
/'
print @buffer
print " "

/* Start the table creation statement */
select @buffer = "create table " + @objname
print @buffer
select @buffer = space(12) + " ("
print @buffer
select @max2 = count(*)
  from syscolumns c,
       systypes t
 where c.id = @objid
   and c.usertype *= t.usertype

/* Print out each column of this table */
select  @count2 = 1
while (@count2 < (@max2 ))
  begin
    select @buffer = space(16) + convert(char(24),c.name) +
           convert(char(20),(t.name +
           substring(" (" + convert(varchar(3),c.length) + ")", 1, 1 +
           6 * (charindex(t.name, "varchar/varbinary"))))) +
           substring("not nullnull", 1 + c.status, 8) + ","
      from syscolumns c,
           systypes t
     where c.id = @objid
       and c.usertype *= t.usertype
       and colid=@count2
    print @buffer
    select @count2 = @count2 + 1
  end
select @buffer = space(16) + convert(char(24),c.name) +
       convert(char(20),(t.name +
       substring(" (" + convert(varchar(3),c.length) + ")", 1, 1 +
       6 * (charindex(t.name, "varchar/varbinary"))))) +
       substring("not nullnull", 1 + c.status, 8)
  from syscolumns c, systypes t where c.id = @objid
   and c.usertype *= t.usertype
   and colid=@count2
print @buffer
select @buffer = space(12) + " )"
print @buffer
print "go"
print ""

select @objid = object_id(@objname)

/*
**  See if the object has any indexes.
**  Since there may be more than one entry in sysindexes for the object,
**  this select will set @indid to the index id of the first index.
*/

select @indid = min(indid)
  from sysindexes
 where id = @objid
   and indid > 0
   and indid < 255

while @indid != NULL
  begin
    select @unique = '',
           @type = '',
           @dup = '',
           @duprow = '',
           @alwdup = '',
           @segment = ''
    /*
    **  Determine the index type by figuring out if it's a
    **  clustered or nonclustered index.
    */

    if @indid = 1 select @type = "clustered"
    if @indid > 1 select @type = "nonclustered"

    /*
    **  Now we'll check out the status bits for this index and
    **  build an english description from them.
    */

    /*
    **  See if the index is unique (0x02).
    */
    if exists
      ( select *
          from master.dbo.spt_values v, sysindexes i
         where i.status & v.number = v.number
           and v.type = "I"
           and v.number = 2
           and i.id = @objid
           and i.indid = @indid )
      begin
        select @unique = ltrim(rtrim(v.name))
          from master.dbo.spt_values v, sysindexes i
         where i.status & v.number = v.number
           and v.type = "I"
           and v.number = 2
           and i.id = @objid
           and i.indid = @indid
      end

      /*
      **  See if the index is ignore_dupkey (0x01).
      */
      if exists
        ( select *
            from master.dbo.spt_values v,
                 sysindexes i
           where i.status & v.number = v.number
             and v.type = "I"
             and v.number = 1
             and i.id = @objid
             and i.indid = @indid )
        begin
          select @dup = v.name + space(1)
            from master.dbo.spt_values v,
                 sysindexes i
           where i.status & v.number = v.number
             and v.type = "I"
             and v.number = 1
             and i.id = @objid
             and i.indid = @indid
        end

        /*
        **  See if the index is ignore_dup_row (0x04).
        */
        if exists
          ( select *
              from master.dbo.spt_values v,
                   sysindexes i
             where i.status & v.number = v.number
               and v.type = "I"
               and v.number = 4
               and i.id = @objid
               and i.indid = @indid )
          begin
            select @duprow = v.name + space(1)
              from master.dbo.spt_values v,
                   sysindexes i
             where i.status & v.number = v.number
               and v.type = "I"
               and v.number = 4
               and i.id = @objid
               and i.indid = @indid
          end

        /*
        **  See if the index is allow_dup_row (0x40).
        */
        if exists
          ( select *
              from master.dbo.spt_values v,
                   sysindexes i
             where i.status & v.number = v.number
               and v.type = "I"
               and v.number = 64
               and i.id = @objid
               and i.indid = @indid )
          begin
            select @alwdup = v.name + space(1)
              from master.dbo.spt_values v, sysindexes i
             where i.status & v.number = v.number
               and v.type = "I"
               and v.number = 64
               and i.id = @objid
               and i.indid = @indid
          end

        /*
        **  Add the location of the data.
        */
        select @segment = s.name
          from syssegments s,
               sysindexes i
         where s.segment = i.segment
           and i.id = @objid
           and i.indid = @indid

        if ( @segment = 'default' )
          begin
            select @segment = NULL
          end
        else
          begin
            select @segment = @segment + space(1)
          end

        select @name = name
          from sysindexes
         where id = @objid
           and indid = @indid

        if ( @dup != '' )
          begin
            if ( datalength(@with) > 0 ) select @with = @with + ", "+@dup
            else select @with = @with + @dup
          end

        if ( @duprow != '' )
          begin
            if ( datalength(@with) > 0 ) select @with = @with + ", "+@duprow
            else select @with = @with + @duprow
          end

        if ( @alwdup != '' )
          begin
            if ( datalength(@with) > 0 ) select @with = @with + ", "+@alwdup
            else select @with = @with + @alwdup
          end

        if ( @with != '' and datalength(@with) > 0 )
          select @with = "with " + @with

        if ( @unique = '' )
          select @unique = @type
        else
          select @unique = @unique + space(1) + @type

        print "create %1! index %2! on %3!",@unique,@name,@objname

        /*
        **  First we'll figure out what the keys are.
        */

        select @i2 = 1,
               @buffer = NULL

        while ( @i2 <= 16 )
          begin
            select @thatkey = index_col(@objname, @indid, @i2)
            if ( @thatkey = NULL ) goto itsdone
            if ( @buffer != NULL )
              begin
                select @buffer = @buffer + ", "
                print @buffer
              end
            else
              begin
                select @buffer = space(22) + "( "
                print @buffer
              end
            select @buffer = space(24) + @thatkey
            /*
            **  Increment @i2 so it will check for the next key field.
            */
            select @i2 = @i2 + 1
          end

        itsdone:
        print @buffer
        select @buffer = space(22)+ ")"
        print @buffer
        if ( @with != null ) print "%1!", @with
        if ( @segment != NULL ) print "on %1!", @segment
        print "go"
        print " "

        /*
        **  Now move @indid to the next index.
        */
        select @last = @indid
        select @indid = NULL
        select @indid = min(indid)
          from sysindexes
         where id = @objid
           and indid > @last
           and indid < 255
  end

go

if object_id('sp_genddl') is not null
begin
    print '<<< Created procedure dbo.sp_genddl >>>'
    grant execute on dbo.sp_genddl to public
end
else
begin
    print '<<< Failed creating proc dbo.sp_genddl >>>'
end
go