Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2002
    Location
    USA-CA
    Posts
    36

    Smile Unanswered: To modify sp_spaceused: I need used table space ONLY

    Hi All,

    I have the question.
    I've wrote the following ksh code

    ...
    for CurrentDBTable in $DB_Tables
    do

    DB_Spaces=`${SYBASE}/bin/isql -b -D${CurrentDBName} -SSYB_SID -Usa -P <<EOF
    sp_spaceused $CurrentDBTable
    go
    EOF`

    echo $CurrentDBName "," $DB_Spaces

    done
    ...

    The output will be like this:

    cmm_db , ADI_LIST 315 16 KB 8 KB 0 KB 8 KB (1 row affected) (return status = 0)
    cmm_db , API_SYNCHRONIZE 0 16 KB 2 KB 0 KB 14 KB (1 row affected) (return status = 0)

    The point is that I need to print out the only parameter -
    a space used by each table from each DataBase.
    In the other words I don't need all this stuff:
    0 16 KB 2 KB 0 KB 14 KB (1 row affected) (return status = 0)

    The only info I need is DataBase, TableName, UsedSpace.
    Could you advice me how to implement it?

    Many Thanks In Advance.
    John
    John Smith

  2. #2
    Join Date
    Apr 2002
    Posts
    2
    Hi John,

    I've written a perlscript some time ago that does almost the same thing.
    Drop me an email if youre interested.

    // Jakob

  3. #3
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78
    Use AWK and try this:

    # reserved
    echo $DB_Spaces | awk '{print $1, $3}'

    or

    # data + index_size
    echo $DB_Spaces | awk '{print $1, $5+$7}'

    Bye bye

    Sebastian

  4. #4
    Join Date
    Mar 2002
    Location
    New Jersey
    Posts
    7
    You can use the following select below :

    SELECT object_name = O.name,
    rw_cnt = ROWCNT(X.doampg),
    tot_space = 2 * SUM( CONVERT( INT, RESERVED_PGS( I.id, I.doampg ) + RESERVED_PGS( I.id, I.ioampg ))),
    data_space = 2 * SUM( CONVERT( INT, RESERVED_PGS( I.id, I.doampg ))),
    Indx_space = 2 * SUM( CONVERT( INT, RESERVED_PGS( I.id, I.ioampg )))
    from sysindexes I,
    sysindexes X,
    sysobjects O
    WHERE O.type = 'U'
    AND O.id >= 100
    AND O.id = I.id
    AND X.id = O.id
    AND X.indid IN ( 0, 1 )
    AND O.id = object_id(@objname)
    GROUP BY O.id, O.name, X.doampg

    Regards,
    Vijay

  5. #5
    Join Date
    Mar 2012
    Posts
    1

    I get 0's for this query

    Quote Originally Posted by vchebolu View Post
    You can use the following select below :

    SELECT object_name = O.name,
    rw_cnt = ROWCNT(X.doampg),
    tot_space = 2 * SUM( CONVERT( INT, RESERVED_PGS( I.id, I.doampg ) + RESERVED_PGS( I.id, I.ioampg ))),
    data_space = 2 * SUM( CONVERT( INT, RESERVED_PGS( I.id, I.doampg ))),
    Indx_space = 2 * SUM( CONVERT( INT, RESERVED_PGS( I.id, I.ioampg )))
    from sysindexes I,
    sysindexes X,
    sysobjects O
    WHERE O.type = 'U'
    AND O.id >= 100
    AND O.id = I.id
    AND X.id = O.id
    AND X.indid IN ( 0, 1 )
    AND O.id = object_id(@objname)
    GROUP BY O.id, O.name, X.doampg

    Regards,
    Vijay
    I commented out the O.id = object_id(@objname) and ran this on my server, all returned columns had a value of 0. Any idea why? This can't be true on my production server on a well used db right?

    Thanks

    PS Does anyone know what table sp_spaceused accesses and/or what db the sp resides in? I'm new to Sybase.

  6. #6
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    114
    Hello,
    You can the code from sp_spaceused, and then retreive whatever you need.
    How to:
    Use sybsystemprocs
    go
    sp_helptext sp_spaceused
    go


    And thats it, returns the code from the sp, that you can create your own.

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •