USE sybsystemprocs
GO

CREATE PROC
--------------------------------------------------------------------------------
--
--     Copyright (c) 1993-2013 Software Gems Pty Ltd
--
--     Derek Asirvadem  26 Jun 13
--
--------------------------------------------------------------------------------
--
--  Stripped down version of the commercial sp_HelpSpace_sg V4.0/Fragment level
--      for Sybase ASE 15.x
--      for public consumption
--
--------------------------------------------------------------------------------
--
--  This code lists all Device Fragments (sysusages rows) in the target
--  database.  Each Fragment is classified by Type (Data/Log/Mixed/Empty) and
--  reporte once.  The LogTotal is reported if the Log is valid (discrete).
--  It also:
--  1. Provides foundation code for you to expand (eg. report fragmentation or
--     disk usage metrics).
--
--------------------------------------------------------------------------------

    sp_HelpFragment_sg

AS

    DECLARE	@WhoAmI	VARCHAR(30)
    SET @WhoAmI = OBJECT_NAME(@@PROCID, DB_ID('sybsystemprocs')),

    -- Licensing, fragmentation & device stats removed

    IF CHARINDEX("15.",@@version) = 0
        BEGIN
        -- 20400, need 15.0 or greater
        -- RAISERROR 20400, @WhoAmI
        PRINT "This proc requires ASE 15.0 or greater"
        -- otherwise simply change the sysdevices::sysusages join
        GOTO EXIT_ERR
        END
        
    IF @@TRANCOUNT > 0
        BEGIN
        -- 17260, Can't run %1! from within a transaction.
        RAISERROR 17260, @WhoAmI
        GOTO EXIT_ERR
        END

EXEC_UTE:

    SELECT [Title] = CONVERT(CHAR(18), GETDATE(), 21) +
        @@SERVERNAME + "." + DB_NAME(dbid) + " Device Fragments"

    SELECT  [Type]      = "Empty",      -- Empty fragments
            [Device]    = name,
            [AllocMB]   = size * @@PAGESIZE / 1048576,
            [UnusedMB]  = ( size - CURUNRESERVEDPGS( dbid, lstart, unreservedpgs ) ) * @@PAGESIZE / 1048576,
            [UnusedPct] = ( size - CURUNRESERVEDPGS( dbid, lstart, unreservedpgs ) ) / size * 100
        FROM master..sysdevices D,
             master..sysusages  U
        WHERE   D.vdevno = U.vdevno
            AND dbid     = DB_ID()
            AND segmap   = 0            -- Empty
    
    UNION
    SELECT  [Type]      = "Data",       -- discrete Data fragments
            [Device]    = name,
            [AllocMB]   = size * @@PAGESIZE / 1048576,
            [UnusedMB]  = ( size - CURUNRESERVEDPGS( dbid, lstart, unreservedpgs ) ) * @@PAGESIZE / 1048576,
            [UnusedPct] = ( size - CURUNRESERVEDPGS( dbid, lstart, unreservedpgs ) ) / size * 100
        FROM master..sysdevices D,
             master..sysusages  U
        WHERE   D.vdevno   = U.vdevno
            AND dbid       = DB_ID()
            AND segmap    != 0          -- exclude Empty
            AND segmap & 4 = 0          -- exclude any with Log component
    
    UNION
    SELECT  [Type]      = "Log",        -- discrete Log fragments
            [Device]    = name,
            [AllocMB]   = size * @@PAGESIZE / 1048576,
            [UnusedMB]  = NULL,         -- cannot be determined per fragment
            [UnusedPct] = NULL          -- CURUNRESERVEDPGS() does not apply to Log
        FROM master..sysdevices D,
             master..sysusages  U
        WHERE   D.vdevno = U.vdevno
            AND dbid     = DB_ID()
            AND segmap   = 4            -- discrete Log
    
    -- Log Alloc & Unused can be determined, for total only.  Values are transient due to activity
    UNION
    SELECT  [Type]      = "Log",
            [Device]    = CHAR(255) + " Total",
            [AllocMB]   = LCT_ADMIN( "num_logpages",         dbid ) * @@PAGESIZE / 1048576,
            [UnusedMB]  = LCT_ADMIN( "logsegment_freepages", dbid ) * @@PAGESIZE / 1048576,
            [UnusedPct] = LCT_ADMIN( "logsegment_freepages", dbid ) / LCT_ADMIN( "num_logpages", dbid ) * 100
    
    UNION
    SELECT  [Type]      = "Mixed",
            [Device]    = name,
            [AllocMB]   = size * @@PAGESIZE / 1048576,
            [UnusedMB]  = NULL,
            [UnusedPct] = NULL
        FROM master..sysdevices D,
             master..sysusages U
        WHERE   D.vdevno   = U.vdevno
            AND dbid       = DB_ID()
            AND segmap & 4 = 4          -- include any Log component
            AND segmap    != 4          -- exclude discrete Log
    
    ORDER BY 1, 2

EXIT_OK:
    RETURN 0

EXIT_ERR:
    RETURN 1

GO
