use sybsystemprocs
go
checkpoint
go
set nocount on
go
print 'Generic stored procedure used by Sybase Job Scheduler Template jobs'
go
-- Remove the sp_jst_get_index_names if it exists before attempting to
-- create it
if exists( select 1 from sysobjects 
           where name = 'sp_jst_get_index_names'
             and type = 'P' 
             and (sysstat & 7) = 4 )
    drop proc sp_jst_get_index_names
go
/*
** sp_jst_get_index_names
** Version: 3.0
**
** This procedure gets the names of all the databases for the current
** server and creates a comma-separated list of them.  The list is 
** returned in the out parameter.
**
** This stored procedure were developed as part of the ASE Job Scheduler
** templates provided by Sybase. It must be installed on the target
** ASE prior to running jobs created from Sybase templates that 
** specify its use.
**
** This stored proc is the called at job execution.  It is a support
** stored procedure and all values for it come from other stored
** procedures.
**
** Parameter definitions
** @table_name - the name of the table for which we want index names
** @index_names - a comma-separated list of names of indexes
**                   
*/
create procedure sp_jst_get_index_names( @table_name varchar(255),
                                         @index_names varchar(2560) out)
as
begin

    print 'sp_jst_get_index_names( @table_name = %1! )', @table_name

    declare @ndx_name varchar(255) 
            , @cnt int
            , @sp_name varchar(30)

    -- Initialize variables
    select @cnt = 0
         , @sp_name = 'sp_jst_get_index_names'
         , @index_names = NULL

    declare db_cursor cursor
    for select si.name from sysindexes si, sysobjects so
                       where so.name = @table_name 
                         and so.id = si.id
                         and si.indid > 0 
                         and si.indid < 255
    for read only
    open db_cursor
    fetch db_cursor into @ndx_name
    while( @@sqlstatus = 0 )
      begin
          if( @ndx_name <> NULL )
            begin
                if( @cnt = 0 )
                    select @index_names = @ndx_name
                else
                    select @index_names = @index_names + "," + @ndx_name

                select @cnt = @cnt+1
                fetch db_cursor into @ndx_name
            end
      end

    close db_cursor

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