<pre>
use sybsystemprocs
go

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

create proc sp_getddl (@table_name varchar(30))
as

/* 
   Written by Vinh T. Le (vtl@tiac.net)

   Modified 3/6/97 to allow dbo who does not own a table to
   get the DDL on a table owned by someone else (the 1st).
   Also, those who don't own a table can get the DDL on a table
   owned by dbo
*/

set nocount on

declare @id int,@cnt int,@maxcol int,@maxlen int
declare @object varchar(30),@msg varchar(125),@username varchar(30)
select @username = null
/* select @id=object_id(@table_name) */

select @id = id from sysobjects where name=@table_name and uid = user_id() and type ='U'

if (@id is null)
begin
   if user_name() = 'dbo'
      begin
         set rowcount 1
         select @id = id,@username = user_name(uid) from sysobjects where name=@table_name 
         set rowcount 0
         setuser @username
      end
   else
      select @id = id from sysobjects where name=@table_name and user_name(uid) = 'dbo'
end

if (@id is null)
begin
   print 'Table "%1!" does not exist',@table_name
   if (@username is not null)
      setuser
   return 1
end

create table #tempddl
(
col_order     tinyint         not null,
col_name      varchar(30)     not null,
dtype         varchar(15)     not null,
dprec         tinyint             null,
dscale        tinyint             null,
dlength       tinyint         not null,
dstat         tinyint         not null,
dfault_id     int                 null,
ddl_text      varchar(125)        null,
ddl_stat      varchar(125)        null
)

/* Change 3/6/97 : Eliminate useless clustered index to make it run a lot faster */
/* Useless because we print according to col_order without sort */
/* create unique clustered index #tempddl_key on #tempddl(col_order) */

insert #tempddl
select c.colid,c.name,t.name,c.prec,c.scale,c.length,c.status,cdefault,null,null
from syscolumns c,systypes t
where c.id=@id and c.usertype=t.usertype

update #tempddl
set ddl_text=rtrim(ltrim(dtype))+'(' + ltrim(str(dprec,3)) + ',' + ltrim(str(dscale,3)) + ')' 
where dtype in ('numeric','decimal')

update #tempddl
set ddl_text=rtrim(ltrim(dtype))+'(' + ltrim(str(dlength,3)) + ')' 
where dtype in ('char','varchar','binary','varbinary')

update #tempddl
set ddl_text=rtrim(ltrim(dtype))
where ddl_text is null

update #tempddl
set ddl_stat='not null'
where dstat = 0 or dtype='bit'

update #tempddl
set ddl_stat='    null'
where dstat = 8

update #tempddl
set ddl_stat='identity'
where dstat & 128 = 128

/* Get default */
update #tempddl
set a.ddl_stat=b.text
from #tempddl a,syscomments b 
where a.dfault_id=b.id

/* Adjust spaces to pretty up the output */
select @maxlen=max(datalength(ddl_stat)) from #tempddl
update #tempddl
set ddl_stat=space(@maxlen - datalength(ddl_stat)) + ddl_stat



select @cnt=1
select @maxcol = max(col_order) from #tempddl
select @maxlen=max(datalength(col_name)) + 5 from #tempddl
print 'create table %1! ',@table_name
print '('
while ( @cnt <= @maxcol )
begin
  select @msg=col_name + space(@maxlen - datalength(col_name)) + ddl_text + space(17 - datalength(ddl_text)) + ddl_stat
   from #tempddl where col_order = @cnt
   if (@cnt < @maxcol)
   begin
      select @msg=@msg + ','
   end
   print @msg
   select @cnt = @cnt +1
end
print ')'
print 'go'

/* Get indexes. Most of this section is copied from sp_helpindex */

declare @indid int,@indname varchar(30) /* the index id of an index */
declare @keys varchar(200)		/* string to build up index key in */
declare @inddesc varchar(68)		/* string to build up index desc in */
/* declare @imsg varchar(90) */
declare @len1 int, @len2 int

/*
**  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 = @id   /* object_id(@table_name) */
and indid > 0 and indid < 255

if @indid is null
begin
   print ''
   print '/* Object ID = %1! */',@id
   if (@username is not null)
   begin
      print '/* Ownner : %1 */',@username
      setuser
   end
   else
   begin
      select @username = user_name()
      print '/* Owner : %1! */',@username
   end
   print ''
   return (0)
end

/*
**  Now check out each index, figure out its type and keys and
**  save the info in variables that we'll print out at the end.
*/

while @indid is not null
begin
/*  First we'll figure out what the keys are. */
   declare @i int
   declare @thiskey varchar(30)
   declare @lastindid int

   select @keys = "", @i = 1

   while @i <= 16
   begin
      select @thiskey = index_col(@table_name, @indid, @i)
      /* select @thiskey = index_col(convert(varchar(20),@id),@indid, @i) */
     
      if @thiskey is null
      begin
         /* goto keysdone */
         break
      end
      if @i > 1
      begin
         select @keys = @keys + ","
      end

      select @keys = @keys + index_col(@table_name, @indid, @i)
      select @i = @i + 1

   end

/*
**  When we get here we now have all the keys.
*/
/* keysdone: */
/*
**  Initialize the index description by figuring out if it's a
**  clustered or nonclustered index.
*/
   select @keys = rtrim(ltrim(@keys))
   if @indid = 1
   begin
      select @inddesc = "clustered"
   end
   if @indid > 1
   begin
      select @inddesc = "nonclustered"
   end

   /*
   **  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 = object_id(@table_name)
      and i.indid = @indid)
      begin
         select @inddesc = v.name + ' ' + @inddesc 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 = object_id(@table_name)
         and i.indid = @indid
      end

/*
**  See if the index is ignore_dupkey (0x01).
*/
   declare @dup varchar(125),@locat varchar(125)
   select @locat = "",@dup = ""

   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 = object_id(@table_name)
      and i.indid = @indid)
      begin
         select @dup = 'ignore_dupkey' /* v.name */
         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 = object_id(@table_name)
         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 = object_id(@table_name)
      and i.indid = @indid)
      begin
         select @dup = 'ignore_dup_row'  /* v.name */
         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 = object_id(@table_name)
         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 = object_id(@table_name)
      and i.indid = @indid)
      begin
         select @dup = 'allow_dup_row'  /* v.name */   
         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 = object_id(@table_name)
         and i.indid = @indid
      end
   
/*
**  Add the location of the data.
*/
/* select @inddesc = @inddesc + " located on " + s.name */
   select @locat = s.name
      from syssegments s, sysindexes i
      where s.segment = i.segment
      and i.id = object_id(@table_name)
      and i.indid = @indid
   
   /* Get index name  */
   select @indname=name from sysindexes where id = object_id(@table_name)
          and indid = @indid

/* Print create index statement  */
   print 'create %1! index %2! on %3! (%4!)',@inddesc,@indname,@table_name,@keys
   if (@dup != "")
   begin
      print 'with %1!', @dup
   end
   if (@locat != "default")
   begin
      print 'on %1!', @locat
   end
   print 'go'

   select @lastindid = @indid
   select @indid = NULL
   select @indid = min(indid) from sysindexes
           where id = object_id(@table_name)
           and indid > @lastindid
           and indid < 255
end

print ''
print '/* Object_id = %1! */',@id
if (@username is not null)
begin
   print '/* Owner : %1! */',@username
   setuser
end
else
begin
   select @username = user_name()
   print '/* Owner : %1! */',@username
end
print ''
return (0)

go

IF OBJECT_ID('sp_getddl') IS NOT NULL
begin
    PRINT '<<< CREATED PROC dbo.sp_getddl >>>'
    GRANT EXECUTE ON dbo.sp_getddl TO public
end
ELSE
begin
    PRINT '<<< FAILED CREATING PROC dbo.sp_getddl >>>'
end
go
