<pre>
use sybsystemprocs
go

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

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

/* %A%
*/
/***************************************************************
**
** N.E.W.
** Project	: WOW!
** Subproject	: Database
**
** Author	: Shawn Wang (swang@newcorp.com or swang@virtualogic.com)
** Create Date	: 6/3/97
** Delta Date	: %G% %U%
** Module Name	: %M%
** Module Name	: sp_u_rebuild_index_ddl.stp
** Syntax	: exec sp_u_rebuild_index_ddl [table_name]
** Description	: Designed as a database maintenance utility to
**		: generate index dll statement on a user table
**		: or all user tables on a user database.
**		: 1> drop all non-clustered indexes;
**		: 2> drop all clustered indexes;
**		: 3> re-build all clustered indexes;
**		: 4> re-build all non-clustered indexes;
**
** Comments	: A lot of people of Sybase performance-tuning group
**		: have contributed to the ideas of this procedure.
**		: Special thanks to John McVicker of Sybase, and
**		: Pablo Sanchez of SiliconGraphics
**
** Weakness	: Don't work for table constraints
**		: 
** Revision Hist:
** Release	: %I%
**
** 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."
    goto endd
  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
  declare @unique_phrase char(7), @clstd_phrase char(10)
  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)

  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')

  /* drop non-clustered indexes */
  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 >= 2               -- non-clustered indexes
    and indid <= 254
    and status2 & 2 != 2

    if (@indcnt = 0)
    begin
--      print "Table '%1!' does not have indexes.", @tbl_name
      goto endd1
    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
	goto endd1

      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 @cnt = @cnt + 1
    end

endd1:
  end

  update #t_table set flag = 1 where flag != 1

  /* drop clustered indexes */
  while exists (select * from #t_table where flag = 1)
  begin
    set rowcount 1

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

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

    set rowcount 0

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

    select @index_name = name, @idx_status = status
    from sysindexes
    where id = @objid
    and indid = 1
    and status2 & 2 != 2

    if @index_name is null
      goto endd2

    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')
endd2:
  end

  update #t_table set flag = 2 where flag != 2

  /* create clustered indexes */
  while exists (select * from #t_table where flag = 2)
  begin
    set rowcount 1

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

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

    set rowcount 0

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

    select @index_name = name, @idx_status = status
    from sysindexes
    where id = @objid
    and indid = 1
    and status2 & 2 != 2

    if @index_name is null
      goto endd3

    select @idx_phrase1 = 'create'

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

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

    /* index columns */
    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)

    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 @idx_status & 1 = 1
    begin
      if char_length(@with_phrase) > 2
        select @with_phrase=", ignore_dup_key "
      else
        select @with_phrase="with ignore_dup_key "
    end

    if @idx_status & 4 = 4
    begin
      if char_length(@with_phrase) > 2
        select @with_phrase=", ignore_dup_row "
      else
        select @with_phrase="with ignore_dup_row "
    end

    if @idx_status & 64 = 64
    begin
      if char_length(@with_phrase) > 2
        select @with_phrase=", allow_dup_row "
      else
        select @with_phrase="with allow_dup_row "
    end

    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("with sorted_data")
    insert #t_index values('go')

endd3:
  end

  update #t_table set flag = 3 where flag != 3

  /* create non-clustered indexes */
  while exists (select * from #t_table where flag = 3)
  begin
    set rowcount 1

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

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

    set rowcount 0

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

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

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

    /* index columns */
    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
	goto endd4

      select @idx_phrase1 = 'create'

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

      select @idx_phrase1 =@idx_phrase1+' nonclustered index '+@index_name+' on'
      select @idx_phrase2 = '  dbo.' + @tbl_name + '('
      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(@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 @idx_status & 1 = 1
      begin
	if char_length(@with_phrase) > 2
          select @with_phrase=", ignore_dup_key "
        else
          select @with_phrase="with ignore_dup_key "
      end

      if @idx_status & 4 = 4
      begin
	if char_length(@with_phrase) > 2
          select @with_phrase=", ignore_dup_row "
        else
          select @with_phrase="with ignore_dup_row "
      end

      if @idx_status & 64 = 64
      begin
	if char_length(@with_phrase) > 2
          select @with_phrase=", allow_dup_row "
        else
          select @with_phrase="with allow_dup_row "
      end

      if @idx_status & 512 = 512
      begin
	if char_length(@with_phrase) > 2
          select @with_phrase=", sorted_data "
        else
          select @with_phrase="with sorted_data "
      end

      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 @cnt = @cnt + 1
    end

endd4:
  end

  select * from #t_index
  return 0
endd:
  return 1
end
go

