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

    Smile Unanswered: I need a script that will show the following info: <DBName> <TableName> <OccupSpace&g

    Hello All,

    I have a problem.
    I need a script that will show the following info in the form:

    <DataBaseName> <TableName> <OccupiedSpace>

    The input for the script will be parameter <Parameter1>
    that tells do not to show info for the Tables that
    <OccupiedSpace> less then <Parameter1>

    I need this script for Sybase DB.
    Please, help me.

    A lot of thanks in advance.
    John Smith.

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    sp_spaceused will give you detailed counts of space used in tables.

    Try capturing the output of sp_helptext sp_spaceused to see how they do it. It's all stored in system tables so you could re-create it if you needed..

    But I suppose you could do something like this in a shell script or perl script etc.:
    Code:
    SELECT name FROM master..sysdatabases
    
    foreach name
      use name
      sp_help /* will show you all tables in current DB */
    
      foreach table that is status 'user table' {
        sp_spaceused name
        if space > something
          display database.name, table.name, spaceused
      } /* end foreach */
    } /* end foreach */
    Thanks,

    Matt

  3. #3
    Join Date
    Apr 2002
    Location
    USA-CA
    Posts
    36
    Thanks a lot. It's a brilliant!
    But I still have a problem.
    I need this script to be written on ksh.
    That's why Your code below doesn't working.
    Could you help me to write it in ksh.
    Thanks a lot.

    #!/usr/bin/ksh

    $SYBASE/bin/isql -Usa -P -SSYB_SID << EOF

    SELECT name FROM master..sysdatabases
    go


    foreach name
    use name
    sp_help /* will show you all tables in current DB */

    foreach table that is status 'user table' {
    sp_spaceused name
    if space > something
    display database.name, table.name, spaceused
    } /* end foreach */
    } /* end foreach */

    EOF
    John Smith

  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Oh that was just pseudocode... To help put some ideas on the table so to speak.
    Thanks,

    Matt

  5. #5
    Join Date
    Apr 2002
    Location
    USA-CA
    Posts
    36

    I need your advice

    Hi,

    Thanks a lot again for the idea. I've started to implement it.


    But I have some additional problem.
    In my case a store procedure sp_help
    gives a great number of records both for tables, triggers, views, etc.
    I've tried to filter this great record by using awk.
    But awk writes me that the record is too long...


    Do you know some another way to recieve a list of tables
    in the current DB?


    Many thanks, again.
    John.

    ...
    for CurrentDBName in $DB_Names
    do
    echo "CurrentDBName = " $CurrentDBName

    cmd_1=`${SYBASE}/bin/isql -b -D${CurrentDBName} -SSYB_SID -Usa -P <<EOF
    go
    sp_help
    go
    EOF`

    echo $cmd_1 >> file1.tmp

    done

    awk '{print $i}' file1.tmp
    John Smith

Posting Permissions

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