/* Job Scheduler Template */
/*
** Messages for sp_jst_rebuild_tables
**
** 17733, "There is no table named '%1!'."
** 19048, "Procedure %1!, parameter %2!, was not supplied, NULL or empty."
** 19146, "Not enough space to run Reorg Rebuild command on table %1!."
*/

-- ISSUES
-- should we offer all dump db options here?
--
use sybsystemprocs
go
checkpoint
go
set nocount on
go
print 'Creating Stored Procedures for Sybase Reorg Rebuild Tables Template '
go
-- Remove the sp_jst_rebuild_tables if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_rebuild_tables'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_rebuild_tables
go
/* 
** sp_jst_rebuild_tables
** Version: 3.0
** 
** This stored procedure runs the reorg rebuild command to rebuild
** an entire table.  It will dump the database if the dump_flag is true.
**
** This procedure was developed as part of the ASE Job Scheduler
** templates provided by Sybase. It must be installed on the target
** ASE prior to running a job created from the "Sybase Reorg Rebuild
** Tables Template"
** 
** This stored proc is the called at job execution.  The Job Scheduler
** GUI will gather the values for each parameter and form the job
** SQL when a job is created from the associated template.
**
** Parameter definitions
** @table_names   - name of one or more tables to be reorganized.
** @db_name       - name of the database containing the table(s); this
**                  value is required to check the size of the table
**                  and verify if there is enough space for the reorg
** @dump_flag     - indicates if database should be dumped after reorg
**                  true = 0, false = 1
** @dump_location - device name or path where db dump will occur.
*/
create procedure sp_jst_rebuild_tables( @table_names varchar(2560),
                                        @db_name varchar(30) = NULL,
                                        @dump_flag int = 1,
                                        @dump_location varchar(128)=NULL )
as
begin

    print 'sp_jst_rebuild_tables(table_names = %1!, db_name = %2!, dump_flag = %3!, dump_location = %4! )', @table_names, @db_name, @dump_flag, @dump_location  

    -- Initialize variables
    declare @sp_name varchar(30)
          , @reorg_cnt int
          , @status int
          , @err int

    select @sp_name = 'sp_jst_rebuild_tables'
         , @reorg_cnt = 0
         , @status = 0
         , @err = 0

    -- check for required input values
    -- must have at least one table name
    select @table_names = rtrim(ltrim(@table_names))
    if( @table_names = NULL )
    begin
        raiserror 19048, @sp_name, 'Table Names'
        return -19048
    end

    -- must have a db name
    select @db_name = rtrim(ltrim(@db_name))
    if( @db_name = NULL )
    begin
        raiserror 19048, @sp_name, 'Database Name'
        return -19048
    end

    --loop through each table specified
    declare @table_name varchar(255)
    declare @cntr int, @end_pos int, @cur_pos int, @len int

    select @len = isnull(datalength(@table_names),0), @cur_pos=1
    while  @len > 0
    begin
        --get the table name: only, next or last
        select @end_pos = patindex('%[,]%', @table_names )
        if @end_pos <= 0
        begin
            -- only or last table name
            select @table_name = ltrim(@table_names)
            select @len = 0
        end
        else
        begin
            -- get the next name
            select @table_name = substring(@table_names,1,@end_pos-1)
            select @table_name = ltrim(rtrim(@table_name))

            select @table_names = stuff(@table_names,1,@end_pos,NULL)
            select @len = isnull(datalength(ltrim(@table_names)),0)
        end

        -- Check if table exists in database
        if not exists( select 1 from sysobjects where name = @table_name )
        begin
            raiserror 17733, @table_name
            select @status = -@@error
            continue
        end

        -- Check if there is enough room for the reorg
        declare @enough_space varchar(5)
        exec sp_jst_check_reorg_space @db_name, @table_name, 
                                      @enough_space out
        if( @enough_space != 'true' )
        begin
            raiserror 19146, @table_name
            select @status = -@@error
            continue 
        end

        --run the reorg rebuild command
        print "reorg rebuild %1!", @table_name  
        exec( "reorg rebuild " + @table_name ) 
        select @err = @@error
        if( @err != 0 )
            select @status = -@err
        else
            select @reorg_cnt = @reorg_cnt + 1

      end --end while

      -- should we dump the db? 
      if( @dump_flag = NULL )
          select @dump_flag = 1

      if( @dump_flag = 0 and @reorg_cnt > 0 )
      begin
          select @dump_location = rtrim(ltrim(@dump_location))
          --dump the current db using the dump command
          exec @err = sp_jst_dump_dbs_to_disk @db_name, @dump_location
          if( @err < 0 )
              select @status = @err
      end

    return @status
end
go
if exists( select 1 from sysobjects
           where name = 'sp_jst_rebuild_tables'
             and type = 'P'
             and (sysstat & 7) = 4 )
begin
    print 'created stored procedure sp_jst_rebuild_tables'
    grant execute on sp_jst_rebuild_tables to public
end
go
