A sp to rebuild indexes for a table or all tables

[sybaseguru]

This stored procedure can be used to create a sql script for all indexes for a given table (you need to provide the tablename) or for building indexes for all user tables in a given database (no tablename). Create it as 'sa' in sybsystemprocs database so you can invoke it from anywhere

/******************************************************************************
**
** Name        : sp__buildindex
** Usage       : sp__buildindex <table_name>
** Date        : 28/02/2002
** Notes       : Will take care of data only locked tables with a clustered index
**
******************************************************************************/

use sybsystemprocs
go
IF EXISTS (SELECT * FROM sysobjects
           WHERE  name = &quot;sp__buildindex&quot;
           AND    type = &quot;P&quot;)
   DROP PROC sp__buildindex
GO
create proc sp__buildindex(@object char(30)=NULL)
as
begin

declare @type   smallint                /* the object type */

select owner      = user_name(o.uid),
       name       = o.name,
       index_name = i.name,
       indexid    = i.indid,
                 status         = status,
                 status2        = status2,
                 createstmt = convert(varchar(127),&quot;N.A.&quot;),
                 keylist    = convert(varchar(127),&quot;N.A.&quot;),
                 endingstmt = convert(varchar(127),&quot;) &quot;),
                 segment                = segment
into   #indexlist
from   sysobjects o, sysindexes i
where  i.id   = o.id
and    o.type = &quot;U&quot;
and      isnull(@object,o.name)=o.name
and      indid > 0
and      status2&2 !=2

if @@rowcount = 0
begin
        if @object is null
        begin
                select convert(varchar(255),&quot;No Indexes found in Current Database&quot;)
        end
        return
end

/* delete multiple rows */
delete #indexlist
from   #indexlist a, #indexlist b
where  a.indexid = 0
and    b.indexid != 0
and    a.name = b.name

update #indexlist
set    createstmt='create'

update #indexlist
set    createstmt = rtrim(createstmt)+' unique'
where  status&2 = 2

update #indexlist
set    createstmt = rtrim(createstmt)+' clustered'
where  indexid = 1
or      (indexid > 1 and status2&512 = 512)   -- data only locked table with a clustered index

update #indexlist
set    createstmt = rtrim(createstmt)+' nonclustered'
where  indexid != 1 and status2&512 != 512

update #indexlist
set    createstmt = rtrim(createstmt)+' index '+rtrim(index_name)+&quot; on &quot;+rtrim(owner)+&quot;.&quot;+rtrim(name)+' ('

declare @count int
select  @count=1

while ( @count < 17 )   /* 16 appears to be the max number of indexes */
begin

        if @count=1
                update #indexlist
                set    keylist=index_col(name,indexid,@count)
                where  index_col(name,indexid,@count) is not null
   else
                update #indexlist
                set    keylist=rtrim(keylist)+&quot;,&quot;+index_col(name,indexid,@count)
                where  index_col(name,indexid,@count) is not null

        if @@rowcount=0 break

        select @count=@count+1
end

update #indexlist
set endingstmt=rtrim(endingstmt)+&quot; with ignore_dup_key&quot;
where status&1 = 1

update #indexlist
set endingstmt=rtrim(endingstmt)+&quot; with ignore_dup_row&quot;
where status&4 = 4

update #indexlist
set endingstmt=rtrim(endingstmt)+&quot; with allow_dup_row&quot;
where status&64 = 64

select rtrim(convert(varchar(255),createstmt+keylist+endingstmt)+convert(char(1),0x0A)+'go')
from #indexlist
order by owner,name,indexid
return(0)
end
go
grant execute on sp__buildindex to public
go
exit

 

[pauljt]

I'm currently writing a similar stored proc to this.

I am debating whether the indid>0 should be
indid>0 and indid<255.

My problem is that my 'dynamic' reindexer currently
finds a 'system index' on a table and tries to delete it
(it fails anyway) and then tries to rebuild it.

Any comments on this?

 

[sybaseguru]

Paul,

I will need to look at it carefully. However, it is really an academic questiion as you cannot under normal circumstances drop or recreate a system index. You may get an error in your log for the failed operation(s) (drop and rebuild) but should not matter. I have used this routine on large mission critical databases many times and it is fine. You may try it on a test database first. good luck