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_database_names if it exists before attempting to
-- create it
if exists( select 1 from sysobjects 
           where name = 'sp_jst_database_names'
             and type = 'P' 
             and (sysstat & 7) = 4 )
    drop proc sp_jst_database_names
go
/*
** sp_jst_database_names
** Version: 1.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
** @database_names - a comma-separated list of names of databases
**                   on the server, except model and tempdb
*/
create procedure sp_jst_database_names( @database_names varchar(500) out)
as
begin

    declare @db_names varchar(128) 
          , @cnt int

    --Initialization
    select @cnt = 0

    declare db_cursor cursor
    for select name from master..sysdatabases
                    where name not in ('model','tempdb')
    for read only
    open db_cursor
    fetch db_cursor into @db_names
    while( @@sqlstatus = 0 )
      begin
          if( @db_names <> NULL )
            begin
                if( @cnt = 0 )
                    select @database_names = @db_names
                else
                    select @database_names = @database_names + "," +
                                             @db_names

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

    close db_cursor
    print 'database names = %1!',  @database_names
    print '@@sqlstatus = %1!',  @@sqlstatus

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