<pre>
use sybsystemprocs
go

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

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

/* @(#) sp_u_create_index_ddl.stp 1.1@(#)
*/
/***************************************************************
**
** N.E.W.
** Date		:
** Project	: WOW!
** Subproject	: Database
**
** Author	: Shawn Wang
** Create Date	: 05/10/1997
** Delta Date	: 05/14/97 09:19:51
** Module Name	: sp_u_create_index_ddl.stp
** Syntax	: exec sp_u_create_index_ddl [table_name]
** Description	: Designed as a reverse-engineering tools to
**		: generate index dll statement on a user table
**		: or all user tables on a user database.
** Revision Hist:
** Comments	: Don't work for table constraints
** Release	: 1.1
** Input Parms	:
** Output Parms	:
**
******************************************************************
*/
begin

  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(75))

  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
  declare @unique_phrase char(7), @clstd_phrase char(10)
  declare @idx_phrase1 varchar(75), @idx_phrase2 varchar(75)
  declare @idx_phrase3 varchar(75), @exist_phrase varchar(75)
  declare @exist_phrase2 varchar(75), @exist_phrase3 varchar(75)

  select @unique_phrase = 'unique', @clstd_phrase = 'clustered'
  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 > 0
    and indid < 255
    and status2 & 2 != 2

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

    while (@cnt <= @indcnt)
    begin
      select @index_name = name, @idx_status = status
      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 = "drop index " + @tbl_name + "." + @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 @idx_phrase1 = 'create'

      if @idx_status & 2 = 2
        select @idx_phrase1 = @idx_phrase1+' '+@unique_phrase

      if @idx_status & 16 = 16
        select @idx_phrase1 = @idx_phrase1+' '+@clstd_phrase

      select @idx_phrase1 = @idx_phrase1+' index '+@index_name+' on'
      select @idx_phrase2 = '  dbo.' + @tbl_name + '(', @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(@idx_phrase1)
      insert #t_index values(@idx_phrase2)
      insert #t_index values('go')

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

      select @cnt = @cnt + 1
    end

endd:
  end

  select * from #t_index
  return 0
end
go

