SEE ALSO

SAMPLE OUTPUT

 ----------------------------------------------
 You are 'SYS_INSTALL' as 'dbo' under SPID 10
        on  with role(s): sa, sso, oper

 -DATABASE:          Monday Oct 11 1999 11:18AM
 ========

 Database: xrm_db                  dbid:5
 DB owner: xrm_mgr                 suid:6
 Creation: Apr  1 1999  9:42AM     MB:      52

 -PLACEMENT:
 =========

 id Database            id Database
 -- ------------------- -- -------------------
 4  sybsystemprocs
 5  xrm_db <<<<<<<<<<<<
 6  xrm_sod01_db
 7  xrm_security_db
 8  sim_db

 -OPTIONS:
 =======

 - Select into/bulkcopy/pllsort
 - Trunc log on chkpt
 - Abort tran on log full

 -IN USE BY:
 =========

 All DBs  # # # # # # # # # # # # # # # # # #
 This DB  xrm_db is NOT in use

 -ROLES:
 =====

 sa         sso        oper       replication
 ---------- ---------- ---------- ------------
 sa
 SYS_OPER
 SYS_INSTAL
 SCRIPT_SVR

 -SERVER:
 ======

 Running  ####################################
 Idle     ##################################

 SQL      #############
 I/O      ####################################

 Received ####################################
 Sent     ###############################
 Errors: 2

 Read     #######
 Write    ####################################
 Errors: 0

 -ENGINES:
 =======

 Nr   Status   # Pr   Online since
 0     online         0       Oct  6 1999 11:48AM

 -DATA:
 ====

         Size MB      Used MB      Full %      Free MB
         40            30.8          77.1         9.2
 -LOG:
 ===

         Size MB      Used MB      Full %      Free MB
         12            0.1            0.8           11.9

 -ALLOCATED:
 =========

 Device         Usage      Size MB
 datadev1               -       Data       -   40
 logdev1                -       Log        -   10
 logdev1                -       Log        -   2

 -AVAILABLE: (max used Virtual Device Nr = 3  )
 =========

 Device   VDN     Total MB     Free MB
 datadev1               2       28
 logdev1                3       13
 master                 0        1
 sysprocsdev            1       0
                          + ------     + -----
                           387         42
 ----------------------------------------------
         see also 'sp__dbuse usage'

xxx

AUTHOR

Q Vincent Yin (umyin@mctrf.mb.ca), Sep 1995

DESCRIPTION

This proc can handle usertypes, defaults and rules that are not covered by the original proc sp_depends. For tables, procs, etc, that are covered by sp_depends, this proc will simply call sp_depends. It prints usage and quits if invoked without arguments. Otherwise:

For each line printed by this proc:

  If @format=null, output is in tabular format similar to sp_depends.
  If @format='drop', output is in isql format.

For example,

  exec sp__depends 'my_rule', 'drop'

will print (not execute) isql scripts that would unbind my_rule from all attached columns and usertypes, and then drop my_rule. By running the generated isql script, you won't encounter this frustrating error:

  Msg 3716, Level 16, State 1:
  The rule 'my_rule' cannot be dropped because it is bound to one or more column.

BUGS

@format='drop' doesn't guarentee the successful dropping of usertype because the usertype may have been used by some tables and procs.

I didn't pay much attention to the owners of objects since all objects at our site are owned by dbo.

USAGE

 Proc_name                      Order Parameter
 ------------------------------ ----- ------------------------------
 sp__depends                        1 @objname varchar(30) NOT NULL
 sp__depends                        2 @format varchar(30) NOT NULL
 sp__depends                        3 @dont_format char(1) NOT NULL

SAMPLE OUTPUT

 object                         dependant
 ------------------------------ ------------------------------
 sp_procxmode                   sysobjects
 sp_validlang                   syslanguages
 sp_getmessage                  sysusermessages
 sp_getmessage                  sysmessages
 sp_getmessage                  syslanguages
 sp_getmessage                  sp_validlang
 sp_configure                   sysattributes
 sp_configure                   sysdevices
 sp_configure                   sysconfigures
 sp_configure                   syscurconfigs

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Basic information about disk devices

USAGE

sp__diskdevice [@devname]

@devname: device name you are interested in - defaults to printing all dump devices

SEE ALSO

sp__helpdevice, sp__dumpdevice

SAMPLE OUTPUT

 1> exec sp__diskdevice
 ****** PHYSICAL DISK DEVICES (Mirror info after device name) ******
 Device Name         Physical Name                   size     alloc    free
 ------------------- ------------------------------- -------- -------- --------
 datadev             /disk1/sybase10/datadev.dat       20.0MB   20.0MB    0.0MB
 datadev2            /disk1/sybase10/datadev2.dat      19.5MB   17.0MB    2.5MB
 datadev3            /disk1/sybase10/datadev3.dat      10.0MB    4.0MB    6.0MB
 master              d_master                          17.0MB   16.5MB    0.5MB
 sybsecurity         /disk1/sybase10/sybsecurity.da     5.0MB    5.0MB    0.0MB
 sysprocsdev         /disk1/sybase10/sysprocsdev.da    10.0MB   10.0MB    0.0MB

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Basic information about dump devices

USAGE

sp__dumpdevice [@devname]

@devname: device name you are inetested in - defaults to printing all dump devices

SEE ALSO

sp__helpdevice, sp__diskdevice

SAMPLE OUTPUT

 1> sp__dumpdevice
 1> exec sp__dumpdevice
 Device Name          Physical Name
 -------------------- --------------------------------------------------
 tapedump1            /dev/rmt4
 tapedump2            /dev/rst0

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Lists potentially missing indexes. Assumes that your foreign key relationships are potential joins and compares keys with actual indexes, listing any keys that do not have associated indexes. This proc will list a key relationship based on fields a,b,c & d if there were no index on a, a&b, a&b&c and so on, assuming that the dba has done his work and would not have an index that was not sufficiently selective (ie. if you decide a is good enough for an index your relationship a,b,c,d should use it in its joins). If this proc does not find the missing index, your model is not sufficiently defined.

USAGE

sp__find_missing_index [ @objname ] [ @p1]

@objectname gives you missing indexes for that object

@p1 if passed will give full output suitable for programs, but too long for humans to read.

SAMPLE OUTPUT

 1> exec sp__find_missing_index
 No Indexes Found in Current Database

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

List flow of procedures in current db. Goes 6 levels. Does this from sysdepends. See also sp__read_write for another good optimization tool. I think the output is readable but...

USAGE

sp__flowchart [ @objname ] [ @p1]

@objectname gives you flow only from 1 object

@p1 if passed will give full 30 character by 6 level output (otherwise the output is

parsed to 15 characters wide strings x 4 levels to fit on the screen).

SAMPLE OUTPUT

 1> exec sp__flowchart
 level 1            level 2            level 3            level 4
 ------------------ ------------------ ------------------ ------------------
 ap_insert_server   ap_insert_system

AUTHOR

Andrew Zanevsky, AZ Databases, Inc.

DESCRIPTION: Searches syscomments table in the current database for occurences of a combination of strings. Correctly handles cases when a substring begins in one row of syscomments and continues in the next.

USAGE

sp__grep @parameter [,@case]

@parameter describes the search:

string1 {operation1 string2} {operation2 string 3} ... where - stringN is a string of characters enclosed in curly brackets not longer than 80 characters. Brackets may be omitted if stringN does not

contain spaces or characters: +,-,&; operationN is one of the characters: +,-,&.

Parameter is interpreted as follows:

1.Combine the list of all objects where string1 occurs.

2.If there is no more operations in the parameter, then display the list and stop. Otherwise continue.

3.If the next operation is + then add to the list all objects where the next string occurs; else if the next operation is - then delete from the list all objects where the next string occurs; else if the next operation is & then delete from the list all objects where the next string does not occur (leave in the list only those objects where the next string occurs);

4.Goto step 2.

Parameter may be up to 255 characters long & may not contain <Line Feed> characters. Please note that operations are applied in the order they are used in the parameter string (left to right). There is no other priority of executing them. Every operation is applied to the list combined as a result of all previous operations.

Number of spaces between words of a string matters in a search (e.g. "select *" is not equal to "select *"). Short or frequently used strings (such as "select") may produce a long result set.

- @case: i = insensitive / s = sensitive (default).

SAMPLE OUTPUT

list all objects where string 'employee' occurs;

sp__grep employee

list all objects where string 'employee' occurs in any case (upper, lower, or mixed), such as 'EMPLOYEE', 'Employee', 'employee', etc.;

sp__grep employee, i

list all objects where either both strings 'employee' and 'salary' occur or string 'department' occurs, and string 'trigger' does not occur;

sp__grep 'employee&salary+department-trigger'

list all objects where string "select FirstName + LastName" occurs;

sp__grep '{select FirstName + LastName}'

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Gives number of select / update /delete /insert / revoke / and execute grants for each group and type of object. Useful to summarize what groups have priviliges to do what.

SEE ALSO

sp__helprotect, sp__objprotect

SAMPLE OUTPUT

   1> sp__groupprotect
   2> go
   type grp             tot    sel    upd    del    ins    rev    exe
   ---- --------------- ------ ------ ------ ------ ------ ------ ------
   P    g_mon6          27     0      0      0      0      0      0
   P    public          27     0      0      0      0      0      9
   R    g_mon6          6      0      0      0      0      0      0
   R    g_monitor       6      0      0      0      0      0      0
   S    g_mon6          57     0      0      0      0      0      0
   S    g_monitor       57     0      0      0      0      0      0
   S    public          57     16     0      0      0      0      0
   U    g_mon6          33     0      0      0      0      0      0
   U    g_monitor       33     0      0      0      0      0      0
   U    public          33     11     0      0      0      0      0
   V    g_mon6          3      0      0      0      0      0      0
   V    g_monitor       3      0      0      0      0      0      0
   V    public          3      0      0      0      0      0      0

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Pretty version of sp_help. sp_help scrolls off screen and is ugly. List objects in current database (if @objname undefined) or list table columns (if @objname defined). The proc is slow....

USAGE

sp_help [@objname]

if @objname is defined, will list information about specific object if that object exists. If the object doesnt exist, it will try to print all objects that contain the string fragment @object. EXAMPLE

sp__help "pmt_" Info about all objects with "pmt_" in the name

sp__help server Info about table server (with column listing)

SEE ALSO

sp__help calls the procedures sp__helpcolumn and sp__helpindex when an object is passed as a parameter.

SAMPLE OUTPUT

   1> sp__help
   1> exec sp__help
    Name                 Owner                Object_type
    -------------------- -------------------- ----------------------
    alerts               dbo                  user table
    audit_trail          dbo                  user table
    comn_database        dbo                  user table
    comn_dumpdevices     dbo                  user table
    comn_syscolumns      dbo                  user table

    comn_sysdevices      dbo                  user table
    comn_sysindexes      dbo                  user table
    comn_syslocks        dbo                  user table
   1> sp__help authors
    Name                 Owner                Object_type
    -------------------- -------------------- ----------------------

    authors              dbo user             table

   table name    insert trigger  update trigger  delete trigger
   ------------- --------------- --------------- ---------------
   authors       authors_ins     ...........     ............

   Column_name   Type            Nulls Default_name    Rule_name
   ------------- --------------- ----- --------------- --------
   au_id         id 0
   au_lname      varchar(40)     0
   au_fname      varchar(40)     0
   phone         char(12)        0      phonedflt
   address       varchar(40)     1

   INDEX KEY c = clustered u = unique
    a = allow dup row s = suspect

   Table Name           Index Name  c u a s List of Index Keys
   -------------------- ----------- - - - - ----------------------------
   authors              auidind     Y Y     au_id

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

List columns for given table

USAGE

sp__helpcolumn @objname , @p1

@objname can be any valid table or view. If null returns all columns.

@p1 if set will not reformat columns

note the columns are sorted by column id if an object is selected and by column name if not.

SEE ALSO

sp__collist

SAMPLE OUTPUT

   1> sp__helpcolumn server
   1> exec sp__helpcolumn
    Column name          Type         I   Null Dflt Rule Table
    -------------------- ------------ --- ---- ---- ---- --------------------
    action               char(18)       0 Yes            audit_trail
    allow_null           tinyint        0 Yes            comn_syscolumns
    allow_updates        smallint       0 Yes            server_configures
    attribute            char(30)       0 Yes            schedule_history
    attribute            varchar(127)   0 Yes            schedule_attributes
    audit_trail          tinyint        0 No             personal_preferences
    benchmark            int            0 Yes            server_statistics
    benchmark            int            0 Yes            summary_statistics
    ...

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Show standard database information in a concise format

SEE ALSO

The following procedures are especially useful while creating new databases and attempting to perform optimal allocation of space: sp__helpdb, sp__helpdbdev, sp__helpdevice

USAGE

sp_helpdb shows information about all databases

sp__helpdb [ @dbname ] prints specific information about given database.

SAMPLE OUTPUT

 1> sp__helpdb statsdb
 name size_data size_log owner
 ---------------- -------------------- -------------------- ----------
 statsdb 7.000000 2.000000 sa

 Database Name Device Name Size Usage
 --------------- --------------- -------------------- ---------------
 statsdb data3 2.000000 data only
 statsdb datadevice 2.000000 data only
 statsdb datadevice 3.000000 data only
 statsdb log 2.000000 log only
 1> sp__helpdb
 1> exec sp__helpdb
 key   description             key   description
 ---   -----------             ---   -----------
 si    select into/bulkcopy    ro    read only
 tl    trunc. log on chkpt     do    dbo use only
 cr    no chkpt on recovery    su    single user
 cl    crashed during load     ab    abort tran
 ds    database suspect

 ****** DATABASE CONFIGURATION *******
  database             data     log      owner      si tl cr cl ds ro do su ab
  -------------------- -------- -------- ---------- -- -- -- -- -- -- -- -- --
  master (1)                  5 N/A      sa
  mis (7)                    15        6 sa
  model (3)                   2 N/A      sa
  pubs2 (8)                   4        2 sa
  statsdb (6)                10 N/A      sa            Y
  sybsecurity (5)             5 N/A      sa            Y
  sybsystemprocs (4)         14        3 sa         Y  Y
  tempdb (2)                  7 N/A      sa         Y  Y

  total space used total data total log
  ---------------- ---------- ----------
       73.00            62.00      11.00

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Show device to database breakdown. Which devices are used by database.

SEE ALSO

The following procedures are especially useful while creating new databases and attempting to perform optimal allocation of space: sp__helpdb, sp__helpdevice, sp__helpdbdev.

USAGE

sp__helpdbdev [ @dbname ]

if @dbname parameter is passed, only show information for given database

SAMPLE OUTPUT

 1> sp__helpdbdev

 Database Name Device Name Size Usage
 --------------- --------------- -------------------- ---------------
 master master 2.000000 data and log
 master master 2.000000 data and log
 master master 3.000000 data and log
 migrator datadevice 10.000000 data and log
 model master 2.000000 data and log
 pubs2 master 2.000000 data and log
 tempdb master 2.000000 data and log

 1> exec sp__helpdbdev
  Database Name   Device Name     Size                 Usage
  --------------- --------------- -------------------- ---------------
  master          master                      1.500000 data and log
  master          master                      3.000000 data and log
  mis             datadev2                    5.000000 data only

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

list defaults information in current database

USAGE

sp__helpdefault [ @objectname ]

if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.

SEE ALSO

sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview

BUGS

These procedures only read row one of syscomments to determine useful information regarding defaults, rules, and views. It is concievable (though unlikely) that a very long select statement could cause necessary data to be in row two, resulting in ugly output.

SAMPLE OUTPUT

 1> sp__helpdefault

  Default Name         Times Used Definition
  -------------------- ---------- -------------------------------------

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Just the info you need, in the format you want. Split between dump and disk devices. Can also be used to show which databases use a given device.

SEE ALSO

The following procedures are especially useful while creating new databases and attempting to perform optimal allocation of space: sp__helpdevice, sp__helpdbdev, sp__helpdb.

USAGE

sp__helpdevice summary information on all devices

sp__helpdevice @devicename details for particular device

SAMPLE OUTPUT

 1> exec sp__helpdevice

 Device Name          Physical Name
 -------------------- --------------------------------------------------
 tapedump1            /dev/rmt4
 tapedump2            /dev/rst0

 Device Name         Physical Name                   size     alloc    free
 ------------------- ------------------------------- -------- -------- --------
 datadev             /disk1/sybase10/datadev.dat       20.0MB   20.0MB    0.0MB
 datadev2            /disk1/sybase10/datadev2.dat      19.5MB   17.0MB    2.5MB
 datadev3            /disk1/sybase10/datadev3.dat      10.0MB    4.0MB    6.0MB
 master              d_master                          17.0MB   16.5MB    0.5MB
 sybsecurity         /disk1/sybase10/sybsecurity.da     5.0MB    5.0MB    0.0MB
 sysprocsdev         /disk1/sybase10/sysprocsdev.da    10.0MB   10.0MB    0.0MB

 1> sp__helpdevice datadevice
 Device Name         Physical Name                   size     alloc    free
 ------------------- ------------------------------- -------- -------- --------
 datadevice          /disk1/sybase10/datadev.dat       20.0MB   20.0MB    0.0MB

 db_name                        size                 usage
 ------------------------------ -------------------- ------------------
 migrator                       10.000000            data and log
 statsdb                         2.000000            data only

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Lists users AND aliases in current database. The alias feature is one that is NOT in sp_helpuser. This is a quick method of seeing who has access to particular data. Groups with no users are also shown.

USAGE

sp__helpgroup [ @groupname ]

if @groupname provided, it will only provide information about that specific group.

SEE ALSO

sp__helplogin, sp__helpuser

SAMPLE OUTPUT

 1> exec sp__helpgroup
 Group_name     Login_name     Alias User_name      Default_db
 -------------- -------------- ----- -------------- --------------
 g_mon6         mon6                 mon6           master
 g_monitor      monitor              monitor        master
 navigator_role N.A.           N     N.A.           N.A.
 oper_role      N.A.           N     N.A.           N.A.
 public         sa                   dbo            master
 public         statsdbo       Y     dbo            statsdb
 public         ebarlow              ebarlow        statsdb
 replication_ro N.A.           N     N.A.           N.A.

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

show index information in current database

USAGE

sp__helpindex [ @objectname, ] [ @width ]

if @objectname is defined, will print index information just for current object. Parameter @width allows printing in modes greater than 80 columns. It may sound weird, but if @objectname is a procedure, it will give any indexes that are used by tables dependant on the procedure (useful for tuning).

SAMPLE OUTPUT

 1> sp__helpindex
 INDEX KEY: c = clustered u = unique
 a = allow dup row s = suspect
 Table Name Index Name c u a s List of Keys
 -------------------- ----------- - - - - -----------------------------

 alerts XPKalerts Y Y hostname,srvname
 applications XPKAccounti Y Y dbname
 audit_trail XPKaudit_tr Y Y date,login_name
 comn_database XPKdatabase Y Y srvname,dbname
 comn_dumpdevices XPKcomn_dum Y Y srvname,devname
 comn_syssegments XPKcomn_sys Y Y seg_id
 comn_sysusages XPKcomn_sys Y Y srvname,devname,dbname,hostname

 1> exec sp__helpindex
    INDEX KEY:     c = clustered            u = unique
                   a = allow dup row        s = suspect

  Table Name            Index Name  c u a s List of Index Keys
  --------------------- ----------- - - - - -----------------------------------
  alerts                XPKalerts   Y   Y   hostname,srvname
  audit_trail           XPKaudit_tr Y   Y   crdate,login_name
  comn_database         XPKdatabase Y Y     srvname,dbname
  comn_dumpdevices      XPKcomn_dum Y Y     srvname,devname
  comn_syscolumns       XPKcomn_sys Y Y     srvname,dbname,object_id,colname

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Shows both login and remote login information Note: if Running as sa on pre 4.9 releases, the password field will show. If running system 10, roles for logins are shown.

USAGE

sp__helplogin [ @loginname ]

SEE ALSO

sp__helpgroup, sp__helpuser

SAMPLE OUTPUT

 (4.9 Version)
 1> sp__helplogin
 ****** SERVER LOGINS *******
 Login_name     Default_db     RemoteName     RemoteSrvr
 -------------- -------------- -------------- --------------
 cosmic         migrator
 ebarlow        pubs2
 probe          master
 qqq            pubs2
 sa             master

 1> exec sp__helplogin
 ****** SERVER LOGINS *******
  Id   Login_name     Default_db     Sht Lck Exp SA SSO Oper Remote
  ---- -------------- -------------- --- --- --- -- --- ---- ------
  12   a              master
  4    ebarlow        statsdb
  6    george         statsdb
  7    lperry         sybsystemprocs
  11   mis            mis
  13   misread        mis
  10   mon6           master
  9    monitor        master
  2    probe          master
  1    sa             master                     Y  Y   Y
  3    statsdbo       statsdb

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

show mirror information in 3 batches: mirrored devices, disabled mirrors, and confused mirrors. The "Pri" & "Sec" fields mean the primary and mirror are active if starred. The Serial field contains a star if Serial writes - none if Parallel.

USAGE

sp__helpmirror

SAMPLE OUTPUT

 1> sp__helpmirror
 ******* SYBASE MIRROR INFORMATION *******
 MIRRORED DEVICES
 Device Pri Sec Serial Mirror Reads
 --------------- --- --- ------ -------------------------------- -----
 data2 * * * /home/programs/sybase/datax *

 1> sp__helpmirror
 ******* SYBASE MIRROR INFORMATION *******

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

list tables, views, procedures, rules, defaults, and triggers in current database

USAGE

sp__helpobject [ @objectname ]

if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.

SEE ALSO

sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview

BUGS

These procedures only read row one of syscomments to determine useful information regarding defaults, rules, and views. It is concievable (though unlikely) that a very long select statement could cause necessary data to be in row two, resulting in ugly output.

SAMPLE OUTPUT

 1> exec sp__helpobject

 Table Name              Rows     Res KB Usd KB Rows/KB Segment       Cr Date
 ----------------------- -------- ------ ------ ------- ------------- -------
 alerts                  420      176    146      2.87  default       24Jan96
 audit_trail             4        62     6        0.66  default       24Jan96
 comn_database           4        32     6        0.66  default       24Jan96
 comn_dumpdevices        3        32     6        0.50  default       24Jan96
 comn_syscolumns         593      160    138      4.29  default       24Jan96
 comn_sysdevices         2        32     6        0.33  default       24Jan96
 comn_sysindexes         70       32     16       4.37  default       24Jan96
 comn_syslocks           0        16     2        0.00  default       24Jan96

 View Name            Cr Date    Tables Used
 -------------------- ---------- ------------------------------------
 titleview            02/22/95   authors, titles, titleauthor

 Procedure_name                 Owner           Created_date
 ------------------------------ --------------- ------------
 ap_audit_report                dbo             24Jan96
 ap_audit_start                 dbo             24Jan96
 ap_audit_status                dbo             24Jan96

 Rule Name     Times Used Definition
 ------------- ---------- ---------------------------------------------
 pub_idrule    1          @pub_id in ("1389", "0736", "0877", "1622", "1
 title_idrule  2          @title_id like "[0-9][0-9][0-9][0-9]" or @ti

 Default Name         Times Used Definition
 -------------------- ---------- ------------------------------------
 datedflt             1          getdate()
 phonedflt            1          "unknown"
 typedflt             1          "undecided"

 Trigger Name                   Cr Date Ins Cnt Del Cnt Upd Cnt
 ------------------------------ ------- ------- ------- -------
 db_space_ins_trigger           24Jan96 1       1       1
 lock_del_trigger               24Jan96 1       1       1
 person_del_trigger             18Jan96 1       1       1
 person_ins_trigger             18Jan96 1       1       1
 scheduler_ins_trigger          24Jan96 1       1       1
 server_del_trigger             24Jan96 1       1       1

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

list procedure information in current database

USAGE

sp__helpproc [ @objectname ]

if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.

SEE ALSO

sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview

BUGS

These procedures only read row one of syscomments to determine useful information regarding defaults, rules, and views. It is concievable (though unlikely) that a very long select statement could cause necessary data to be in row two, resulting in ugly output.

SAMPLE OUTPUT

 1> exec sp__helpproc

 Procedure_name                 Owner           Created_date
 ------------------------------ --------------- ------------
 ap_audit_report                dbo             24Jan96
 ap_audit_start                 dbo             24Jan96
 ap_audit_status                dbo             24Jan96
 ap_config_info                 dbo             24Jan96
 ap_debug_statsdb               dbo             24Jan96
 ap_diff_db                     dbo             24Jan96
 ap_diff_srvr                   dbo             24Jan96
 ap_get_disk_layout             dbo             24Jan96

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

list protection information for current database

USAGE

sp__helprotect [ @parameter = objectname | username | group ] [@do_system_tables] [@dont_format]

if @parameter is provided then procedure will attempt to print information about only one object (if it exists), group, or user. If no parameter is passed, all objects are displayed. The @do_system_tables parameter, if not null, will include system tables in the output.

SEE ALSO

sp__groupprotect, sp__objprotect

BUGS

helprotect should really be spelled helpprotect, but im sticking with the sybase naming convention.

SAMPLE OUTPUT

 1> sp__helprotect
 ------------------------------
 Grant Execute on ap_get_disk_layout  to public
 Grant Execute on ap_get_ind_to_rebuild  to public

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

list rules information in current database

USAGE

sp__helprule [ @objectname ]

if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.

SEE ALSO

sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview

BUGS

These procedures only read row one of syscomments to determine useful information regarding defaults, rules, and views. It is concievable (though unlikely) that a very long select statement could cause necessary data to be in row two, resulting in ugly output.

SAMPLE OUTPUT

 1> sp__helprule
 Rule Name     Times Used Definition
 ------------- ---------- ---------------------------------------------
 pub_idrule    1          @pub_id in ("1389", "0736", "0877", "1622", "
 title_idrule  2          @title_id like "bu[0-9][0-9][0-9][0-9]"

DESCRIPTION

shows segments information by database. Also shows sizes of segments.Used in conjunction with sp__segment procedure to decipher segment heirarchy.

USAGE

sp__helpsegment [ @segname ]

SAMPLE OUTPUT

 1> exec sp__helpsegment

 Segment Codes:

 U=USER-defined segment on this device fragment
 L=Database LOG may be placed on this device fragment
 D=Database objects may be placed on this device fragment by DEFAULT
 S=SYSTEM objects may be placed on this device fragment

 ******* SERVER SEGMENT MAP *******
  db              segmap      segname         segs device name     size (MB)
  --------------- ----------- --------------- ---- --------------- ---------
  master                    7 system           LDS master             3.00
  master                    7 default          LDS master             3.00
  master                    7 logsegment       LDS master             3.00
  master                    7 system           LDS master             1.50
  master                    7 default          LDS master             1.50
  master                    7 logsegment       LDS master             1.50
  mis                       4 logsegment       L   datadev            3.00
  mis                       4 logsegment       L   datadev            3.00

 1> sp__helpsegment "datadevice"

 Segment Codes:
 U=USER-defined segment on this device fragment L=Database LOG may be placed
 on this device fragment D=Database objects may be placed on this device
 fragment by DEFAULT S=SYSTEM objects may be placed on this device fragment
 ******* SERVER SEGMENT MAP ******* db segmap segname segs device name size
 (MB) --------------- ----------- ----------- ---- --------------- ---------

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

list table information in current database

USAGE

sp__helptable [ @objectname ]

if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.

SEE ALSO

sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview

BUGS

These procedures only read row one of syscomments to determine useful information regarding defaults, rules, and views. It is concievable (though unlikely) that a very long select statement could cause necessary data to be in row two, resulting in ugly output.

SAMPLE OUTPUT

 1> exec sp__helptable

  Table Name              Rows     Res KB Usd KB Rows/KB Segment       Cr Date
  ----------------------- -------- ------ ------ ------- ------------- -------
  alerts                  420      176    146      2.87  default       24Jan96
  audit_trail             4        62     6        0.66  default       24Jan96
  comn_database           4        32     6        0.66  default       24Jan96
  comn_dumpdevices        3        32     6        0.50  default       24Jan96
  comn_syscolumns         593      160    138      4.29  default       24Jan96
  comn_sysdevices         2        32     6        0.33  default       24Jan96
  comn_sysindexes         70       32     16       4.37  default       24Jan96
  comn_syslocks           0        16     2        0.00  default       24Jan96

AUTHOR

Andrew Zanevsky, AZ Databases, Inc.

DESCRIPTION

list text of code. Uses print statement for technical reasons.

USAGE

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

list trigger information in current database

USAGE

sp__helptrigger [ @objectname ]

if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.

SEE ALSO

sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview. Another way to look at triggers is sp__trigger.

BUGS

These procedures only read row one of syscomments to determine useful information regarding defaults, rules, and views. It is concievable (though unlikely) that a very long select statement could cause necessary data to be in row two, resulting in ugly output.

SAMPLE OUTPUT

 1> exec sp__helptrigger

 Trigger Name                   Cr Date Ins Cnt Del Cnt Upd Cnt
 ------------------------------ ------- ------- ------- -------
 db_space_ins_trigger           24Jan96 1       1       1
 lock_del_trigger               24Jan96 1       1       1
 person_del_trigger             18Jan96 1       1       1
 person_ins_trigger             18Jan96 1       1       1
 scheduler_ins_trigger          24Jan96 1       1       1
 server_del_trigger             24Jan96 1       1       1
 server_ins_trigger             24Jan96 1       1       1
 server_upd_trigger             24Jan96 1       1       1

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

There is no easy way to look at user defined types using the current system procs, so here one is.

USAGE

 Proc_name                      Order Parameter
 ------------------------------ ----- ------------------------------
 sp__helptype                       1 @dont_format char(1) NOT NULL

SAMPLE OUTPUT

 TYPE                  DEFAULT       RULE          NULL BASE TYPE
 --------------------- ------------- ------------- ---- ---------------------
 binary                NULL          NULL             1 binary
 tinyint               NULL          NULL             1 tinyint
 smallint              NULL          NULL             1 smallint
 int                   NULL          NULL             1 int
 float                 NULL          NULL             1 float
 numeric               NULL          NULL             1 numeric
 money                 NULL          NULL             1 money
 datetime              NULL          NULL             1 datetime
 intn                  NULL          NULL             1 intn

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Lists users AND aliases in current database. The alias feature is one that is NOT in sp_helpuser. This is a quick method of seeing who has access to particular data. Users whose logins are not in syslogins are listed with a login name of NULL.

SEE ALSO

sp__helplogin, sp__helpgroup

SAMPLE OUTPUT

 1> exec sp__helpuser

 Login_name        User_name         Alias Group_name        Default_db
 ----------------- ----------------- ----- ----------------- -----------------
 sa                dbo                     public            master
 statsdbo          dbo               Y     public            statsdb
 ebarlow           ebarlow                 public            statsdb
 mon6              mon6                    g_mon6            master
 monitor           monitor                 g_monitor         master

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

list views information in current database

USAGE

sp__helpview [ @objectname ]

if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.

SEE ALSO

sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview

BUGS

These procedures only read row one of syscomments to determine useful information regarding defaults, rules, and views. It is concievable (though unlikely) that a very long select statement could cause necessary data to be in row two, resulting in ugly output.

SAMPLE OUTPUT

 1> sp__helpview

 View Name             Cr Date    Tables Used
  -------------------- ---------- --------------------------------------

AUTHOR

Edward Barlow

DESCRIPTION:

Tells you who you are and in which database you are in

USAGE

sp__id

SAMPLE OUTPUT

 1> exec sp__id

 db                   login                id   db name
 -------------------- -------------------- ---- --------------------
 statsdb              sa                   1    dbo

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

sp__indexspace gives details of the space used by index in the database. It is a quick way of finding out information on in the database (did your load succeed...). Another way to look at this information is with the stored procedure sp__indexspace, which sumarizes on an index by index basis. Rows/KB is calculated only on the data plus index pages used. It ignores system overhead. If the Rows/KB field (actual rows in table) is much less than the Max/KB (maximum according to row size - found in rowpage field of sysindexes), the index may be very fragmented and could be in need of rebuilding (drop and recreate it). This is only relevant if there is a signifcant numbers of rows.

USAGE

sp__indexspace [ @table ]

SEE ALSO

sp__helptable

SAMPLE OUTPUT

 1> exec sp__indexspace

 Data Level (Index Type 0 or 1)
 Name                   Rows     Used/Data/Idx KB Rows/KB Segment
 ---------------------- -------- ---------------- ------- ------------
 alerts.XPKalerts       420      176/142/4          2.88  default
 audit_trail.XPKaudit_t 4        62/2/4             0.67  default
 comn_database.XPKdatab 4        32/2/4             0.67  default
 comn_dumpdevices.XPKco 3        32/2/4             0.50  default
 comn_syscolumns.XPKcom 593      160/122/16         4.30  default

 Non Clustered Indexes

 Name                   Rows     Used/Data/Idx KB Rows/KB Segment
 ---------------------- -------- ---------------- ------- ------------
 pbcatcol.pbcatcol_idx  0        16/0/2             0.00  default

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

SEE ALSO

sp__whoactive, sp__isactive, sp__iostat

DESCRIPTION

Unix iostat command equivalent. Does a loop on existing processes and prints only rows in sysprocesses that are doing anything (io or cpu > 0).

BUGS

Does not monitor logins started after process starts.

USAGE

sp__iostat { [ @count ] [, @delay ]

   @count is number of iterations (default=3)
   @delay - delay between stats rows in seconds. Valid values are 1,3,5,10,30,60

SAMPLE OUTPUT

1> exec sp__iostat

 -------- ---------
 00:22:32 No Change

 Time     Spid Login         Cmd              Cpu    Io   Mem  Blk
 -------- ---- ------------- ---------------- ------ ---- ---- ------
 00:22:42 6    NULL          SITE HANDLER     DEAD   DEAD DEAD      0

 -------- ---------
 00:22:52 No Change

AUTHOR

Androw Zanevsky, AZ Databases, Inc. 71232.3446@compuserve.com

SEE ALSO

sp__whoactive, sp__isactive, sp__iostat

DESCRIPTION

Monitors indicators of a given process for specified time and reports activity (CPU, IO, Locks...).

If any indicator changes, then process is active. This proc uses the waitfor command.

USAGE

sp__whoactive @spid [, @delay ]

@spid is the spid to watch

@delay is the monitoring interval in seconds (must be 5,10,20,or 60) - defaults to 5

SAMPLE OUTPUT

        > sp__isactive 1

        time locks cpu phys_io cmd status
        -------- ------ ----------- ----------- ---------------- ----------------
        21:24:51 0 5 0 SELECT running
        21:24:56 0 5 0 SELECT running

        ~+4 sec 0 0 0

        Process shows no activity

AUTHOR

Simon Walker, The SQL Workshop LTD., Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

pretty print locks

USAGE

sp__lock

SEE ALSO

sp__block

sp__lockt

SAMPLE OUTPUT

 1> exec sp__lock

 Type        User               Table                  Page     Cmd
 ----------- ------------------ ---------------------- -------- -----------
 Sh_intent   sa (pid=1)         master..spt_values     0        SELECT
 Bugs:

 Only shows correct object name for objects in current database. If the

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

pretty print table locks

USAGE

sp__lockt

SEE ALSO

sp__block sp__lock

SAMPLE OUTPUT

 1> exec sp__lockt

 spid dbname           objname          type
 ---- ---------------- ---------------- ----------------
 1    master           spt_values       Sh_intent

BUGS

Only shows correct object name for objects in current database. If the

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Unix ls equivalent. lists objects in db

USAGE

sp__ls [ @string ]

@ string: search string - pass in wildcards as you wish (ie. xxx% or ab%cd%). Does a normal Sybase like on the string passed.

SAMPLE OUTPUT

 1> exec sp__ls

 Object_name                    Type Owner           Created_date
 ------------------------------ ---- --------------- --------------------
 alerts                         U    dbo             Jan 24 1996  1:43PM
 ap_audit_report                P    dbo             Jan 24 1996  5:38PM
 ap_audit_start                 P    dbo             Jan 24 1996  5:38PM
 ...

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Prettier monitoring information than is normally shown using sp_monitor. Gives details since the last run.

SEE ALSO

USAGE

 Proc_name                      Order Parameter
 ------------------------------ ----- ------------------------------
 sp__monitor                        1 @desc char(30) NOT NULL
 sp__monitor                        2 @dont_format char(1) NOT NULL

SAMPLE OUTPUT

    text
   -----------------------------------
    Number Of Users =1
    Seconds Since Last Run =76803
    Cpu Busy =0
     Io Busy =0

AUTHOR

Simon Walker, The SQL Workshop LTD.

DESCRIPTION

find tables with either no index or no clustered index. If the table has more than a small number of rows (5 data pages), you may be able to easily improve system performance by adding some.

USAGE

sp__noindex

SAMPLE OUTPUT

 1> exec sp__noindex

 No_Indexes                     Rows        Pages
 ------------------------------ ----------- -----------
 model                                    1           1
 server_syslocks                          0           1
 system_stats_save                     3610         145
 summary_statistics_save               3022         160
 table_space_save                      1028          47
 db_space_save                           90           6
 comn_syslocks                            0           1

 No_Clustered_Index             Rows        Pages
 ------------------------------ ----------- -----------
 pbcattbl                                 0           1
 pbcatcol                                 0           1
 x                                        0           1

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Gives number of select / update /delete /insert / revoke / and execute grants for each object. Can either be by user (parameter) or total.

SEE ALSO

sp__groupprotect, sp__helprotect

SAMPLE OUTPUT

 1> sp__objprotect 'public'

 name                           type sel    upd    del    ins    rev    exe

 ------------------------------ ---- ------ ------ ------ ------ ------ ------
 datedflt                       D    0      0      0      0      0      0
 phonedflt                      D    0      0      0      0      0      0
 typedflt                       D    0      0      0      0      0      0
 byroyalty                      P    0      0      0      0      0      1
 discount_proc                  P    0      0      0      0      0      1
 history_proc                   P    0      0      0      0      0      1
 insert_sales_proc              P    0      0      0      0      0      1
 insert_salesdetail_proc        P    0      0      0      0      0      1
 storeid_proc                   P    0      0      0      0      0      1
 storename_proc                 P    0      0      0      0      0      1

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Returns Extended Stored Procedure Library version number

SAMPLE OUTPUT

 1> exec sp__proclib_version

 -----
  3.80

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

sp__dbspace has had locking problems in busy environments when run programaticly. Use this procedure if you are running space monitoring in a loop.

SEE ALSO

sp__dbspace

USAGE

 Proc_name                      Order Parameter
 ------------------------------ ----- ------------------------------
 sp__qspace                         1 @dont_format char(1) NOT NULL

SAMPLE OUTPUT

 Name                           Percent              Log Pct
 ------------------------------ -------------------- --------------------
 master                                    63.789063             0.000000

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Quick dump of server stats. Can be run in a tight loop to see how things happen on the server. Normally this program will get the current date from the server, and then run in a while loop running the sp__quickstats procedure (parameter of the previously retrieved date). The parameters mean the following: conn is number of non - sa connections. Time is the time taken to run this query. tbllocks are the number of table locks. runnable is the number of non sa processes performing work.

USAGE

sp__quickstats { @starttime }

if @starttime is not passed, will not print out a date field at the end.

SAMPLE OUTPUT

 1> exec sp__quickstats

 blks conn ctime  locks run  tlock %cpu  %io   %idle minutes
 ---- ---- ------ ----- ---- ----- ----- ----- ----- ----------
 0    0    126    0     0    0     17.51  0.00 82.49 10.2

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

This nice proc identifies which tables are read and which are written by stored procedures in the database. Useful for tuning/optimization. See sp__flowchart. read_write is both read & write. The data used by ths proc is not accurate.

USAGE

sp__read_write

SAMPLE OUTPUT

 1> exec sp__read_write

 Tblname                        Sel   Reads Writes R&W   num_rows
 ------------------------------ ----- ----- ------ ----- -----------
 alerts                         0     2     2      2             420
 audit_trail                    0     0     4      0               4
 comn_database                  0     0     0      0               4
 comn_syscolumns                0     0     0      0             593
 comn_sysdevices                0     0     0      0               2
 comn_sysindexes                0     0     0      0              70
 comn_syslocks                  0     0     0      0               0
 comn_syslogins                 0     0     0      0               6
 comn_sysobjects                0     0     0      0             325
 comn_sysusages                 0     0     0      0               4
 comn_sysusers                  0     0     0      0              16
 db_space                       0     0     0      0               3
 db_space_history               0     1     1      1              88
 dflt_configures                0     1     1      1               4

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

reverse engineers aliases

USAGE

sp__revalias

SEE ALSO

sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser

WARNING: Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.

SAMPLE OUTPUT

 1> sp__revalias

 Text
 -----------------------------------------------------------------------
 exec sp_addalias 'probe','dbo'
 exec sp_addalias 'ebarlow','dbo'

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Reverse engineer bindings of user defined types to columns

SAMPLE OUTPUT

 bindings
 -----------------------------------------------------
 exec sp_bindefault option_yn_def,'acc_control.modify'
 exec sp_bindefault option_yn_def,'sod_control.modify'
 exec sp_bindrule option_yn_rule,option_yn_type
 exec sp_bindefault option_yn_def,option_yn_type

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

reverse engineers layout of the databases on a server. Purposly excludes device "master", "model", and "tempdb".

USAGE

sp__revdb

SEE ALSO

sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser

WARNING: Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.

SAMPLE OUTPUT

 1> sp__revdb

 Create Database statsdb on 'data3'=2
  ,'datadevice'=2
  ,'datadevice'=3
  log on 'log'=2
 go
 Create Database migrator on 'datadevice'=10
 go
 Create Database pubs2 on 'master'=2
 go

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

reverse engineers layout of the devices on a server. Purposly excludes device "master", "diskdump", "tapedump1", and "tapedump2".

USAGE

sp__revdevice

SEE ALSO

sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser

WARNING: Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.

SAMPLE OUTPUT

 1> sp__revdevice

 /********* BACKUP DEVICES *********/
 Text
 ---------------------------
 exec sp_addumpdevice 'disk','diskdump','/dev/null',2
 exec sp_addumpdevice 'disk','master_dbdump','/dumps/master_dbdump',2
 exec sp_addumpdevice 'disk','master_logdump','/dumps/master_logdump', 2
 exec sp_addumpdevice 'disk','tapedump1','/dev/rmt4',2
 exec sp_addumpdevice 'disk','tapedump2','/dev/rst0',2

 /****** PHYSICAL DISK DEVICES ******/
 Text
 ----------------------------------------------------------------------
 disk init name='data2',physname='/sybase/data2',vdevno=2,size=1024
 disk init name='data3',physname='/sybase/data3', vdevno=4,size=2000
 disk init name='data',physname='/sybase/data', vdevno=7,size=8096
 disk init name='log', physname='/sybase/log', vdevno=3, size=1024

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

reverse engineers layout of the groups in your current database

USAGE

sp__revgroup

SEE ALSO

sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser

WARNING: Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.

SAMPLE OUTPUT

 1> sp__revgroup

 -------------------------------------------------
 exec sp_addgroup 'developer'
 exec sp_addgroup 'user'

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

reverse engineers indexes in your current database

USAGE

sp__revindex

SEE ALSO

sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser WARNING:

Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.

SAMPLE OUTPUT

 1> sp__reverseindex

 Text
 -------------------------------------------------------------------
 create clustered index XPKalerts on dbo.alerts (hostname,srvname)

 create unique clustered index XPKAccounting on dbo.applications (dbname)
 create unique clustered index XPKaudit_trail on dbo.audit_trail (date,login_name)
 create unique clustered index XPKdatabase on dbo.comn_database (srvname,dbname)
 create unique clustered index XPKcomn_dumpdevices on dbo.comn_dumpdevices

AUTHOR

Created By : "Chris Vilsack"

DESCRIPTION

reverse engineers keys in your current database.

USAGE

sp__revkey

SEE ALSO

sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser, sp__revtable

WARNING: run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.

SAMPLE OUTPUT

 exec sp_primarykey 'sd_region', region_id
 go
 exec sp_primarykey 'sd_site', site_id
 go
 exec sp_primarykey 'sd_table_maint', tbl_name
 go
 exec sp_primarykey 'sd_timeband', timeband_id
 go
 exec sp_primarykey 'acc_control', db_name
 go

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

reverse engineers logins in your current server. Can only be run by sa.

USAGE

sp__revlogin

SEE ALSO

sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser WARNING:

Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.

SAMPLE OUTPUT

 1> sp__revlogin

 ---------------------------------------------
 exec sp_addlogin 'sa','sybase','master'
 exec sp_addlogin 'probe','','master'
 exec sp_addlogin 'ebarlow','ebarlow','statsdb'
 exec sp_addlogin 'xxx','xxx','master'
 exec sp_addlogin 'yyy','yyy','master'
 exec sp_addlogin 'ttt','ttt','statsdb'
 SYSTEM 10 NOTE:

 As the system 10 password is encrypted, the password field is filled

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

reverse engineers mirror information

USAGE

sp__revuser

SEE ALSO

sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser WARNING:

Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.

SAMPLE OUTPUT

 1> sp__revmirror
 ----------------------------------------------------------
 disk mirror name='data2',mirror='/home/programs/sybase/datax'

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

SAMPLE OUTPUT

 exec sp_role 'grant',sa_role    , SYS_OPER
 exec sp_role 'grant',sa_role    , SYS_INSTALL
 exec sp_role 'grant',sa_role    , SCRIPT_SVR
 exec sp_role 'grant',sso_role   , xrm_mgr
 exec sp_role 'grant',sso_role   , SYS_OPER
 exec sp_role 'grant',sso_role   , SYS_INSTALL
 exec sp_role 'grant',sso_role   , SCRIPT_SVR
 exec sp_role 'grant',oper_role  , xrm_admin
 exec sp_role 'grant',oper_role  , SYS_OPER
 exec sp_role 'grant',oper_role  , SYS_INSTALL
 exec sp_role 'grant',oper_role  , SCRIPT_SVR

AUTHOR

Created By : "Chris Vilsack"

DESCRIPTION

reverse engineers rules in your current database.

USAGE

sp__revrule

SEE ALSO

sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser, sp__revtable

WARNING: run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.

SAMPLE OUTPUT

 exec sp_bindrule 'cc1_sd_product_group', 'sd_product_group.fx_risk_linear'
 exec sp_bindrule 'cc2_sd_product_group', 'sd_product_group.int_risk_linear'
 exec sp_bindrule 'option_yn_rule', 'acc_control.active'
 exec sp_bindrule 'option_yn_rule', 'acc_control.backup'
 exec sp_bindrule 'option_yn_rule', 'acc_control.modify'

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

reverse engineers segment layout in your current database

USAGE

sp__revsegment

SEE ALSO

sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser, sp__revsegment WARNING:

Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.

SAMPLE OUTPUT

 1> sp__revsegment
 -------------------------------------------
 exec sp_addsegment 'yyy','datadevice'

AUTHOR

Edward Barlow ( SQL Technologies, inc. ). Modified by Victor Go and Viorel Vlad to add the constraint stuff.

DESCRIPTION

reverse engineers table layout in your current database. This version handles default, check, and primary key constraints.

USAGE

sp__revtable

SEE ALSO

sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser, sp__revtable

WARNING: run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.

SAMPLE OUTPUT

 1> sp__revtable authors

 tbl_txt
 -------------------------------------------
 CREATE TABLE authors
 (
 au_id id NOT NULL,
 au_lname varchar(40) NOT NULL,
 au_fname varchar(40) NOT NULL,
 phone char(12) NOT NULL,
 address varchar(40) NULL,
 city varchar(20) NULL,
 state char(2) NULL,
 country varchar(12) NULL,
 postalcode char(10) NULL
 )

AUTHOR

   Dinyar Ghyara [SMTP:Dinyar_Ghyara_Tk@data.japan.ml.com]
   Thursday, August 05, 1999 4:46 PM

DESCRIPTION

reverse engineers types in your current database

USAGE

sp__revtype

SEE ALSO

sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revtype

SAMPLE OUTPUT

 1> sp__revtype
 -------------------------------------------
 exec sp_addtype application_type," char( 20 )" ,"not null"
 exec sp_addtype ccy_type," char( 5  )" ,"not null"
 exec sp_addtype deal_type_type," char( 10 )" ,"not null"
 exec sp_addtype option_yn_type," char( 1  )" ,"not null"
 exec sp_addtype product_type," char( 10 )" ,"not null"
 exec sp_addtype profit_center_type," char( 20 )" ,"not null"
 exec sp_addtype site_type," char( 10 )" ,"not null"

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

reverse engineers user layout in your current database

USAGE

sp__revuser

SEE ALSO

sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser

WARNING

Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.

SAMPLE OUTPUT

 1> sp__revuser
 -------------------------------------------
 exec sp_adduser 'sa','dbo','developer'
 exec sp_adduser 'xxx','xxx','user'
 exec sp_adduser 'yyy','yyy','user'
 exec sp_adduser 'ttt','ttt','developer'

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

show segmentation

USAGE

sp__segment [ @segment ]

if @segment is passed, will only print info for the given segment

SAMPLE OUTPUT

 1> exec sp__segment

 segment       Data KB     Indx KB     Total
 ------------- ----------- ----------- -----------
 default              2874        1432        4306
 logsegment             16          16          32
 system                  0          16          16
               sum         sum
               =========== ===========
                      2890        1464

 segment       type       indexname                                Size KB
 ------------- ---------- ---------------------------------------- ----------
 default       CLUSTERED  alerts.XPKalerts                         176
 default       CLUSTERED  audit_trail.XPKaudit_trail               62
 default       CLUSTERED  comn_database.XPKdatabase                32
 default       CLUSTERED  comn_dumpdevices.XPKcomn_dumpd           32
 default       CLUSTERED  comn_syscolumns.XPKcomn_syscol           160

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

server summary report

USAGE

sp__server

SEE ALSO

The following procedures are called by sp__server: sp__helpdb, sp__helpdbdev, sp__devspace, sp__helpdevice, sp__helpmirror, sp__vdevno, sp__helpsegment, sp__helplogin.

SAMPLE OUTPUT

 1> exec sp__server

 [ OUTPUT TRUCATED FOR SPACE ]

 ******* SYBASE VERSION *******
 SQL Server/4.9.2/EBF 1950 Rollup/Sun4/OS 4.1.2/1/OPT/Wed Sep 8 00:30:38 PDT 1993

 tl    trunc. log on chkpt     do    dbo use only
 cr    no chkpt on recovery    su    single user
 cl    crashed during load     ab    abort tran
 ds    database suspect

 ****** DATABASE CONFIGURATION *******
 database             data     log      owner      si tl cr cl ds ro do su ab
 -------------------- -------- -------- ---------- -- -- -- -- -- -- -- -- --
 master (1)                  5 N/A      sa
 mis (7)                    15        6 sa
 model (3)                   2 N/A      sa
 pubs2 (8)                   4        2 sa

 total space used total data total log
 ---------------- ---------- ----------
      73.00            62.00      11.00

 Database Name   Device Name     Size                 Usage
 --------------- --------------- -------------------- ---------------
 master          master                      1.500000 data and log
 master          master                      3.000000 data and log
 mis             datadev2                    5.000000 data only
 mis             datadev2                   10.000000 data only
 mis             datadev                     3.000000 log only

 Device Name          Physical Name
 -------------------- --------------------------------------------------
 tapedump1            /dev/rmt4

 Device Name         Physical Name                   size     alloc    free
 ------------------- ------------------------------- -------- -------- --------
 datadev             /disk1/sybase10/datadev.dat       20.0MB   20.0MB    0.0MB
 datadev2            /disk1/sybase10/datadev2.dat      19.5MB   17.0MB    2.5MB
 datadev3            /disk1/sybase10/datadev3.dat      10.0MB    4.0MB    6.0MB

 ******* SYBASE MIRROR INFORMATION *******
    (NO DEVICES ARE MIRRORED)

 vdevno  device
 ------- ------------------------------
     0   master
     1   sysprocsdev
     2   sybsecurity

 db              segmap      segname         segs device name     size (MB)
 --------------- ----------- --------------- ---- --------------- ---------
 master                    7 system           LDS master             3.00
 master                    7 default          LDS master             3.00
 master                    7 logsegment       LDS master             3.00
 master                    7 system           LDS master             1.50
 master                    7 default          LDS master             1.50

 ****** SERVER LOGINS *******

 Id   Login_name     Default_db     Sht Lck Exp SA SSO Oper Remote
 ---- -------------- -------------- --- --- --- -- --- ---- ------
 12   a              master
 4    ebarlow        statsdb
 6    george         statsdb
 7    lperry         sybsystemprocs
 11   mis            mis
 13   misread        mis

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

SEE ALSO

USAGE

 Proc_name                      Order Parameter
 ------------------------------ ----- ------------------------------
 sp__size                           1 @objname varchar(40) NOT NULL

SAMPLE OUTPUT

 Proc_name                      Size    Avail_size  Lines       Avail_lines
 ------------------------------ ------- ----------- ----------- -----------
 mon_authorize_non_sa           6    KB         122           2         253
 mon_rpc_attach                 3    KB         125           1         254
 mon_rpc_connect                2    KB         126           1         254
 sp_configure                   180  KB         -52          73         182
 sp_dboption                    116  KB          12          49         206
 sp_dbupgrade                   73   KB          55          19         236
 sp_getmessage                  33   KB          95          14         241
 sp_loaddbupgrade               6    KB         122           2         253
 sp_procxmode                   28   KB         100          12         243
 sp_prtsybsysmsgs               4    KB         124           1         254
 sp_validlang                   6    KB         122           2         253

(11 rows affected) (return status = 0)

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Prints the @@ variables for your server in a loop. Note this proc is actually 2 procedures (sp__stat and sp__stat2) because the @@ variables seem to be replaced when the procedure is executed not when the loop gets to them (so print @@cpu, wait, print @@cpu, wait, print @@cpu will print the same thing each time).

USAGE

sp__stat @cnt, @delay, @batch

@cnt is the number of rows to print

@delay is the number of seconds delay allowed (must be 1,2,3,4,5,10,30,60)

@batch is a flag - if null will print in real time, if not null, will hold results in #tmp table

and then print them all at once.

NOTES

The Cpu, IO, and Idle values are in ticks.

The net in and net out values are in packets

All the numbers except Users and Runnable processes are from @@variables of the same name. The users is the number of non - system users, the Run field is the number of users doing something. The conn is the number of connections that happened in the interval.

SAMPLE OUTPUT

 1> exec sp__stat

 Usrs Run %Cpu %IO  Secs Conn Net in Net out Reads Writes Errors
 ---- --- ---- ---- ---- ---- ------ ------- ----- ------ ------
 0    0   0    0    3    0    0      0       0     2      0

 Usrs Run %Cpu %IO  Secs Conn Net in Net out Reads Writes Errors
 ---- --- ---- ---- ---- ---- ------ ------- ----- ------ ------
 0    0   33   0    2    0    0      1       0     0      0

 Usrs Run %Cpu %IO  Secs Conn Net in Net out Reads Writes Errors
 ---- --- ---- ---- ---- ---- ------ ------- ----- ------ ------
 0    0   0    0    2    0    0      0       0     0      0

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Shows syntax of a stored procedure. Who can remember how to use sp_addlogin... Wont tell you what is optional and what is required, but it does tell you how to use the proc.

USAGE

sp__syntax [@procname]

you probably should pass in a parameter, otherwise you will get gobs of output.

SAMPLE OUTPUT

 1> exec sp__syntax ap_diff_db

 Proc_name                      Order Parameter
 ------------------------------ ----- ------------------------------
 ap_diff_db                         1 @srv1 char(30)
 ap_diff_db                         2 @db1 char(30)

AUTHOR

Simon Walker, The SQL Workshop LTD.

DESCRIPTION

show all tables/triggers in current database

USAGE

sp__trigger [@tablename]

SEE ALSO

sp__helptrigger

SAMPLE OUTPUT

 1> exec sp__trigger

 table name           insert trigger     update trigger     delete trigger
 -------------------- ------------------ ------------------ ------------------
 alerts               .................. .................. ..................
 audit_trail          .................. .................. ..................
 comn_database        .................. .................. ..................
 comn_dumpdevices     .................. .................. ..................
 comn_syscolumns      .................. .................. ..................
 comn_sysdevices      .................. .................. ..................
 comn_sysindexes      .................. .................. ..................
 comn_syslocks        lock_del_trigger   .................. ..................
 comn_syslogins       syslogin_insert_tr .................. ..................
 comn_sysobjects      .................. .................. ..................

AUTHOR

Simon Walker, The SQL Workshop LTD.

DESCRIPTION

Shows Used Device Numbers

SAMPLE OUTPUT

 1> exec sp__vdevno

 vdevno  device
 ------- ------------------------------
     0   master
     1   sysprocsdev
     2   sybsecurity
     3         -- free --
     4         -- free --
     5         -- free --
     6         -- free --
     7   datadev
     8   datadev3
     9   datadev2

AUTHOR

Simon Walker, The SQL Workshop LTD.

DESCRIPTION

pretty version of sp_who

USAGE

sp__who [ @param ]

if @param is a login, it will only print information for that login. If it is a database, it will only print information about users in that database.

SAMPLE OUTPUT

 1> exec sp__who

 spid loginame   host     program  proc dbname     status   cmd              bk
 ---- ---------- -------- -------- ---- ---------- -------- ---------------- --
 1    sa         gamesrus isql     9056 statsdb    running  SELECT           0
 2    NULL                              master     sleeping NETWORK HANDLER  0
 3    NULL                              master     sleeping MIRROR HANDLER   0
 4    NULL                              master     sleeping AUDIT PROCESS    0

AUTHOR

Androw Zanevsky, AZ Databases, Inc. 71232.3446@compuserve.com

DESCRIPTION

Monitors indicators of a given process for specified time and reports activity (CPU, IO, Locks...).

If any indicator changes, then process is active. This proc uses the waitfor command.

USAGE

sp__whoactive [ @loginname] [, @delay ]

@loginname is the spid to watch. Can pass 'active' or 'all' in place of logins (I hope you

dont have logins 'active' and 'all' on your server :).

@delay is the monitoring interval in seconds (must be 5,10,20,or 60) - defaults to 5

SAMPLE OUTPUT

 1> exec sp__whoactive

    Oct 31 1997 12:23AM
 Activity indicators of all logins during the last  5 seconds

 spid  loginame     locks cpu   i/o    cmd              status   blkd bcnt a
 ----- ------------ ----- ----- ------ ---------------- -------- ---- ---- -
     1 sa               0     0      0 INSERT           running     0    0 @
     2 NULL             0     0      0 NETWORK HANDLER  sleeping    0    0
     3 NULL             0     0      0 MIRROR HANDLER   sleeping    0    0
     4 NULL             0     0      0 AUDIT PROCESS    sleeping    0    0
     5 NULL             0     0      0 CHECKPOINT SLEEP sleeping    0    0

Total: 5 process(es). (* - active, @ - this process.)

AUTHOR

Simon Walker & Ed Barlow (Ed rewrote Simons command)

DESCRIPTION

sp__who that drops awaiting commands and the sa stuff

SEE ALSO

sp__whoactive, sp__isactive, sp__iostat, sp__who

USAGE

sp__whodo [ @param ]

if @param is a login, it will only print information for that login. If it is a database, it will only print information about users in that database.

SAMPLE OUTPUT

 1> exec sp__whodo

 pid loginame  cpu   io    mem   dbname     status   cmd              bk bktime
 --- --------- ----- ----- ----- ---------- -------- ---------------- -- ------
 1   sa        1104  8     1     statsdb    running  INSERT           0  0

AUTHOR

Philippe Wathelet July 1998

DESCRIPTION

This is a substitute for Sybase's standard sp_who stored procedure

The output is clearer without line wrapping. You can help it with:

   isql -U<user> -S<server> -w150

allowing a width of up to 150 characters per line for example

USAGE

sp__whoe [ @login ]

if @login is passed, it will only print information for that login.

SAMPLE OUTPUT

 1> exec sp__whoe