/* Job Scheduler Template */
/*
** Messages for sp_jst_delete_stats
**
** 19048, "Procedure %1!, parameter %2!, was not supplied, NULL or empty."
** 19131, "Cannot delete statistics for table %1!."
** 19132, "Cannot recompile stored procedures and triggers for table %1!."
*/

-- ISSUES
-- do we need to recompile sps & triggers?
--
use sybsystemprocs
go
checkpoint
go
set nocount on
go
-- Remove the sp_jst_delete_stats if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_delete_stats'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_delete_stats
go
print 'Creating Sybase Delete Statistics Template stored procedures'
go
/* 
** sp_jst_delete_stats
** Version: 3.0
**
** This stored procedure is used to delete statistics related to a
** particular table, either the table as a whole or specific columns
** as specified in the columns parameter.  
**
** 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 Delete
** Statistics 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_name - name of the table for which statistics should be
**               deleted
** @columns    - one or more (comma-separated) column names
** @ptn_name   - partition name
*/
create procedure sp_jst_delete_stats ( @table_name varchar(255)=NULL,
                                       @columns varchar(2560) = NULL, 
                                       @ptn_name varchar(255) = NULL )
as
begin

    print 'sp_jst_delete_stats( table_name = %1!, columns = %2!, ptn_name = %3! )', @table_name, @columns, @ptn_name 

    -- supported syntax:  
    --  delete statistics <table_name> [partition data_partition_name]
    --                                 [(column_list)] 
    --
    -- Example:
    --  delete statistics authors(auth_id)
    --

    declare @delete_cmd varchar(1000)
          , @add_columns int
          , @err int
          , @sp_name varchar(30)
          , @ptn_ok int

    -- Initialization
    select @sp_name = 'sp_jst_delete_stats'
         , @ptn_ok = -1

    --  must have a table name
    select @table_name = ltrim(rtrim(@table_name))
    if( @table_name = NULL )
      begin
          raiserror 19048, @sp_name, 'table name' 
          return -@@error
      end

    --start building the command
    select @delete_cmd = 'delete statistics ' + @table_name

    exec @ptn_ok = sp_jst_valid_ase_version '15.0'
    if( @ptn_ok = 0 )
    begin
        select @ptn_name = ltrim(rtrim(@ptn_name))

        if( @ptn_name is not NULL and @ptn_name != "" )
        begin
            select @delete_cmd = @delete_cmd + ' partition ' + @ptn_name
            --print 'partition is %1!', @ptn_name
        end
    end

    --  add column names if supplied
    select @columns = ltrim(rtrim(@columns))
    if( @columns <> NULL )
      begin
        select @add_columns = isnull(datalength(@columns),0)
        if( @add_columns > 0 )
          begin
            select @delete_cmd = @delete_cmd + ' (' + @columns + ')'
          end
      end

    --ready to run the deleted stats command
    print @delete_cmd
    execute( @delete_cmd )
    if( @@error != 0 )
      begin
          raiserror 19131, @table_name
          return -@@error
      end

    --for recompile of procs and triggers referencing the table
    exec @err = sp_recompile @table_name
    if( @err < 0 )
      begin
          raiserror 19132, @table_name
          return -@@error
      end

    return 0
end
go
if exists( select 1 from sysobjects
           where name = 'sp_jst_delete_stats'
             and type = 'P'
             and (sysstat & 7) = 4 )
begin
    print 'sp_jst_delete_stats has been created'
    grant execute on sp_jst_delete_stats to public
end
go
