use sybsystemprocs
go
checkpoint
go
set nocount on
go
--print 'Generic stored procedures used by Sybase Job Scheduler Template jobs'
-- Remove the sp_jst_get_spaceused if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_get_spaceused'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_get_spaceused
go
/*
** sp_jst_get_spaceused
** Version: 3.0
**
** Note: This version of this sproc is compatible with the ASE 15.0 release as
** it uses the new data_pages builtin functions.
**
** This procedure gets the space used for rows and indexes for the
** table specified. This code comes from the second half of the spaceused 
** stored procedure but omits the reporting sql.
**
** 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 whose used space amount is needed
** @usedspace_K - out parameter, row and index space consumed by table
*/
create procedure sp_jst_get_spaceused( @table_name varchar(255),
                                       @usedspace_K int output )
as
begin

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

    if (@table_name = "syslogs") /* syslogs */
    begin
        declare @free_pages     int /* log free space in pages */
              , @clr_pages      int /* log space reserved for CLRs */
              , @total_pages    int /* total allocatable log space */
              , @used_pages_K   int /* allocated log space */
              , @ismixedlog     int  /* mixed log & data database ? */

        select @ismixedlog = status2 & 32768
          from master.dbo.sysdatabases where dbid = db_id()

        select @clr_pages = lct_admin("reserved_for_rollbacks", db_id())
        select @free_pages = lct_admin("logsegment_freepages", db_id())
                                       - @clr_pages
        --print 'clear_pages %1!', @clr_pages
        --print 'free_pages %1!', @free_pages

        select @total_pages = sum(u.size)
        from master.dbo.sysusages u
        where u.segmap & 4 = 4 and u.dbid = db_id()

        --print 'total_pages %1!', @total_pages
        if(@ismixedlog = 32768)
          begin
            /*
            ** For a mixed log and data database, we cannot
            ** deduce the log used space from the total space
            ** as it is mixed with data. So we take the expensive
            ** way by scanning syslogs.
            */
            select @used_pages_K = lct_admin("num_logpages", db_id())

            /* Account allocation pages as used pages */
            select @used_pages_K = @used_pages_K + (@total_pages / 256)
          end
        else
          begin
            /* Dedicated log database */
            select @used_pages_K = @total_pages - @free_pages
                                                - @clr_pages
          end
        select @usedspace_K = @used_pages_K
        --print 'usedspace is : %1!', @usedspace_K
    end --if syslogs

    -- not syslogs table
    else
    begin
        declare @dbobj_name varchar(70)
              , @dbind_name varchar(70)
              , @select_str1 varchar(250)
              , @select_str2 varchar(250)
              , @tabid int

        select @dbobj_name =  'sysobjects'
             , @dbind_name =  'sysindexes'
             , @tabid = object_id( @table_name )

        create table #jstspaceused( object_name varchar(255), 
                                    tabid int,
                                    index_name varchar(255),
                                    indid int,
                                    data_size_K numeric(20,9),
                                    ndx_size numeric(20,9) )

       -- here's what we need in out temp table:
       -- select tableName, indexName, dataSize, indexSize
       -- where indexId=objectId and objectID=indexID
       select @select_str1 = 'insert #jstspaceused ' +
              'select o.name,i.id,i.name,i.indid' +
              ', convert(numeric(20,9),0)' +
              ', convert(numeric(20,9),0)' +
              ' from ' + @dbobj_name + ' o, ' 

        select @select_str2 = @dbind_name + ' i' +
          ' where i.id = ' + convert(varchar(10), @tabid) + 
          ' and o.id = ' +  convert(varchar(10), @tabid ) 

        --print 'insert:  '
        --print '         %1!%2!', @select_str1, @select_str2

        -- insert the temp table data
        exec( @select_str1 + @select_str2 )

        /* calculate the counts for indid > 1
        ** case of indid = 1, 0 are special cases done later
        */
        update #jstspaceused 
           set ndx_size =  convert( numeric(20, 9),
                                    data_pages(db_id(), @tabid, indid) )
         where indid > 1

        /* if indid = 0, we only need the data size */
        update #jstspaceused 
           set data_size_K = convert( numeric(20, 9),
                                   data_pages(db_id(), @tabid, indid) )
         where indid = 0

        /* for indid = 1, since we need get size of data and index pages. */
        update #jstspaceused 
           set ndx_size = convert( numeric(20, 9),
                                   data_pages(db_id(), @tabid, indid) ),
               data_size_K = convert( numeric(20, 9),
                                      data_pages(db_id(), @tabid, 0) )
         where indid = 1

        -- sizes in KB
        declare @data_size_K numeric(11,0)
              , @index_size_K numeric(11,0)
              , @used_space_K numeric(11,0)
              --, @data_select varchar(200)
              --, @ndx_select varchar(200)

        select @data_size_K = sum(data_size_K)*(@@maxpagesize/1024)
          from #jstspaceused 

        select @index_size_K = sum(ndx_size)*(@@maxpagesize/1024)
          from #jstspaceused 

        select @used_space_K = @data_size_K + @index_size_K
        select @usedspace_K = @used_space_K
        --print 'data size kb is : %1!', @data_size_K
        --print 'index size kb is : %1!', @index_size_K
        --print 'usedspace is : %1!', @usedspace_K

    end   --else not syslogs

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