<pre>
use sybsystemprocs 
go

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

create proc sp_u_create_constraint_ddl
@tbl_name varchar(32) = null
as 

/* %A%
*/
/***************************************************************
**
** N.E.W.
** Date		:
** Project	: WOW!
** Subproject	: Database
**
** Author	: Shawn Wang (swang@newcorp.com or swang@virtualogic.com)
** Create Date	: 06/7/1997
** Delta Date	: %G% %U%
** Module Name	: %M%
** Syntax	: exec sp_u_create_constraint_ddl [table_name]
** Description	: Designed as a reverse-engineering tool to
**		: generate constraint dll statements on a user table
**		: or all user tables on a user database.
** Comments	: This can be considered as the companion of a stp,
**		: sp_u_create_index_ddl.
**		: unique 
** Revision Hist:
** Release	: %I%
** Input Parms	:
** Output Parms	:
**
******************************************************************
*/
begin

  if @@trancount = 0
    set chained off

  set transaction isolation level 1

  declare @db_name varchar(32), @index_name varchar(32)
  select @db_name = db_name()

  if (@db_name = 'master') or (@db_name = 'model') or
    (@db_name = 'tempdb') or (@db_name = 'sybsystemprocs') or
    (@db_name = 'sybsecurity') or (@db_name = 'sybsyntax') or
    (@db_name = 'tempdb')
  begin
    print "Please don't run this procedure on system databases."
    return 1
  end

  set nocount on

  create table #t_index(
    col1	varchar(120))

  create table #t_table(
    tbl_name	varchar(32),
    flag	tinyint)

  declare @indid  tinyint         -- index id of unique index
  declare @indcnt tinyint         -- no of index on a table
  declare @objid  int             -- object id of table
  declare @cnt    int             -- index counter
  declare @col_cnt int            -- column counter
  declare @thiskey varchar(30)    -- description of each column
  declare @idx_status smallint, @idx_status2 smallint
  declare @idx_phrase1 varchar(120), @idx_phrase2 varchar(120)
  declare @idx_phrase3 varchar(120), @exist_phrase varchar(120)
  declare @exist_phrase2 varchar(120), @exist_phrase3 varchar(120)
  declare @maxrow smallint, @segno smallint
  declare @with_phrase varchar(120), @segmt_phrase varchar(120)
  declare @alter_phrase varchar(120)

  select @exist_phrase =
    "if exists (select * from sysindexes i, sysobjects o where o.id = i.id "
  select @db_name = db_name()

  if @tbl_name is not null
    insert #t_table values(@tbl_name, 0)
  else
    insert #t_table
    select name, 0 from sysobjects
    where type='U' order by name

  insert #t_index values("use "+@db_name)
  insert #t_index values('go')

  while exists (select * from #t_table where flag = 0)
  begin
    set rowcount 1

    select @tbl_name = tbl_name
    from #t_table where flag = 0

    update #t_table set flag = 1
    where tbl_name = @tbl_name

    set rowcount 0

    select @objid = object_id(@tbl_name), @indcnt = 0

    if @objid = null
    begin
      print "Table '%1!' does not exist in this database.", @tbl_name
      goto endd
    end

    select  @indcnt = max(indid), @cnt = min(indid)
    from sysindexes
    where id = @objid
    and indid >= 1
    and indid <= 254
    and status2 & 2 = 2

    if (@indcnt = 0)
    begin
      print "Table '%1!' does not have constraints.", @tbl_name
      goto endd1
    end

    while (@cnt <= @indcnt)
    begin
      select @index_name = name, @idx_status = status,
	@idx_status2 = status2,
        @maxrow = maxrowsperpage, @segno = segment
      from sysindexes
      where id = @objid
      and indid = @cnt
      and status2 & 2 = 2

      if @index_name is null
	break

      select @exist_phrase2=
	"and o.name = '"+@tbl_name+"' and i.name = '"+@index_name + "')"
      select @exist_phrase3 = "alter table dbo." + @tbl_name + " drop constraint " + @index_name

      insert #t_index values(@exist_phrase)
      insert #t_index values(@exist_phrase2)
      insert #t_index values(@exist_phrase3)
      insert #t_index values('go')

      select @alter_phrase ='alter table dbo.'+@tbl_name+' add'
      if @idx_status2 & 8 = 8
        select @idx_phrase1 = ' '
      else
        select @idx_phrase1 = 'constraint '+@index_name

      if @idx_status & 2048 = 2048
        select @idx_phrase1 = @idx_phrase1+' primary key'
      else if @idx_status & 2 = 2
        select @idx_phrase1 = @idx_phrase1+' unique'

      if @idx_status & 16 = 16
        select @idx_phrase1 = @idx_phrase1+' clustered'
      else
        select @idx_phrase1 = @idx_phrase1+' nonclustered'

--      select @idx_phrase1 = @idx_phrase1+' index '+@index_name+' on'
--      select @idx_phrase2 = '  dbo.' + @tbl_name + '('
      select @idx_phrase2 = '('
      select @col_cnt = 1

      while @col_cnt <= 16
      begin
        select @thiskey = index_col(@tbl_name, @cnt, @col_cnt)

        if @thiskey is NULL
        begin
	  select @idx_phrase3=
	    substring(@idx_phrase2,1,(char_length(@idx_phrase2)-1))
          select @idx_phrase2=@idx_phrase3+')'
          break
        end

        select @idx_phrase2 = @idx_phrase2 + rtrim(@thiskey) + ','
        select @col_cnt = @col_cnt + 1
      end

      insert #t_index values(@alter_phrase)
      insert #t_index values(@idx_phrase1)
      insert #t_index values(@idx_phrase2)

      select @with_phrase="", @segmt_phrase=""

      if @maxrow > 0
      select @with_phrase="with max_rows_per_page="+convert(char(4),@maxrow)+" "

      if @segno > 2
        select @segmt_phrase = "on "+s.name
        from sysindexes i, syssegments s
        where i.id = @objid
        and i.indid = @cnt
        and i.segment = s.segment

      if char_length(@with_phrase) > 2
        insert #t_index values(@with_phrase)
      if char_length(@segmt_phrase) > 2
        insert #t_index values(@segmt_phrase)

      insert #t_index values('go')

--  select @idx_phrase1
--  select @idx_phrase2
--  select 'go'

      select @cnt = @cnt + 1
    end

endd1:
  end

  select * from #t_index
  return 0

endd:
  return 1
end
go

