NAME

sp__auditdb - Audit current database

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

   Checks Common Database Problems
   Lists users in group public if groups are used.
   Warn about lack of groups if no groups exist besides public.
   List users aliased to another non dbo user.
   List aliases without logins (login previously dropped).
   List users without logins (login previously dropped).
   List objects owned by non-dbo (maybe poor code control?).
   Find objects with access to syslogins in them. This
      procedure excludes normal objects like sp__addlogin. Use
      of this procedure will identify potential Trojan horses.
   Find any objects with public access.
   If not master db, list any objects starting with "sp_" that
      are also in master (Trojan horses).
   Database has not had transaction log dump in 24 hours.
   Checks Object / Comment mismatch (hand deleted, or rename)
   Create object permissions granted to users

USAGE

sp__auditdb [@srvname, @hostname ]

External programs that collect errors can pass the parameters parameters (@srvname & @hostname) to the procedure. If these are passed, the procedure will print a slightly different return set that includes error numbers and other information.

SEE ALSO

sp__auditsecurity

ACCESS

This procedure can be only runable by sa because it may reveal information that can help an intruder..

SAMPLE OUTPUT

1> sp__auditdb

   Error
   ---------------------------------------------
   Object get_comn_syslogins has access to syslogins
   Object get_comn_sysusers has access to syslogins
   Object get_comn_sysusers has access to syslogins
   User sa is a member of group public
   Group Public access to object pb_catcol type=P
   Group Public access to object pb_catedt type=P
   Group Public access to object pbcatfmt type=U
   Group Public access to object pbcattbl type=U

NAME

sp__auditsecurity - Audit system security

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

  Reports Users With Passwords like the Username
  Reports Users With Null Passwords
  Reports Users With Short (<=4 character) Passwords
  Reports Users With Master/Model/Tempdb Database As Default (except sa)
  Reports allow updates is set
  Reports Users with stupid passwords like "sybase"....

USAGE

sp__auditsecurity [@print_only_errors,] [@srvname, @hostname ]

if @print_only_errors is not null then prints only errors. Otherwise it will print statements about successes

Programs that collect errors can pass the parameters parameters (@srvname & @hostname) to the procedure. If these are passed, the procedure will print a slightly different return set that includes error numbers and other information.

SEE ALSO

sp__auditdb

ACCESS

This procedure is only runable by sa because it reveals users with weak passwords.

SAMPLE OUTPUT

  1> sp__auditsecurity

  Security Violations
  ------------------------------------------------------
  (No Users With Null Passwords)
  User monitor Has master Database As Default
  User mon6 Has master Database As Default
  User a Has master Database As Default
  Allow Updates is Set
  (Allow Updates is Not Set)
  (No Trusted Remote Logins)

NAME

sp__badindex - List badly formed indexes or those needing statistics

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Identifies bad indexes according to the following rules. Finds indexes containing null, vbl lth, text, or image columns. Find indexes over 30 bytes long or indexes that have never had statistics updated on them. List NC indexes on small tables.

USAGE

sp__badindex [ @tablename ]

SAMPLE OUTPUT

1> exec sp__badindex

 Table/Index Name               Description            Problem Found
 ------------------------------ ---------------------- --------------
 alerts.XPKalerts               Length = 60            >30 Byte Index
 alerts.XPKalerts               srvname char(30)       Allows Null
 audit_trail.XPKaudit_trail     Length = 38            >30 Byte Index
 comn_database.XPKdatabase      Length = 60            >30 Byte Index
 comn_dumpdevices.XPKcomn_dumpd Length = 60            >30 Byte Index
 comn_syscolumns.XPKcomn_syscol Length = 94            >30 Byte Index

NAME

sp__bcp - Produce object-level bcp commands to get server data

AUTHOR

Simon Walker, The SQL Workshop LTD.

DESCRIPTION

Creates bcp in / out shell script that can be used to extract info from the database.

USAGE

sp__bcp {server}, [database], [user], [password], [direction], [extension], [commands]

where...

        {@server} Server name (should really be entered since
              @@servername is rarely defined)
        [@database] Defaults to database procedure is run in
        [@user] Defaults to current username
        [@password] Defaults to current password
        [@direction] "out" or "in". Defaults to out
        [@extension] File extension will default to .dat
        [@commands] Allows you to enter further switching commands
              (-c option to bcp)

BUGS

It is advisable to run the stored procedure through isql in at least 132 column mode (-w132) to stop the crummy (isql) program from inserting linefeeds.

SAMPLE OUTPUT

        1> use master
        1> sp__bcp SYBASE,master,sa,xxx
        echo ""
        echo ""
        echo BCP out table master..spt_committab
        bcp master..spt_committab out spt_committab.dat -Usa -Pxxx
        -SSYBASE -c
        echo ""
        echo ""
        echo BCP out table master..spt_values
        bcp master..spt_values out spt_values.dat -Usa -Pxxx -SSYBASE

NAME

sp__block - Show blocked processes details

AUTHOR

4.9 version: Simon Walker, The SQL Workshop LTD. System 10 version: Ed Barlow

DESCRIPTION

Monitor Blocked Processes

SEE ALSO

sp__block sp__lockt

SAMPLE OUTPUT

  1> sp__block
  SPID User       Host   Program    Blocking on Table    Lock Type
  ---- ---------- ------ ---------- -------------------- ---------------
  7    giraffe                      AARDVARKS_R_US       Update_page-blk
  7    giraffe                      AARDVARKS_R_US       Ex_page-blk

  Blocked SPID Blocked User Host Program Blocked By SPID
  ------------ ------------ ---------- ---------- ---------------
  26           edisking                           7
  26           ediswise                           7
  45           iamrich                            7
  45           iampoor                            7

NAME

sp__checkkey - Creates script you can use to check db referential integrity.

AUTHOR

Ed Barlow

DESCRIPTION

Create script to check foreign key relationships. For example if you have a field stor_id in table sales that indicates the store the sales are for, the script output lists stor_id's in sales that do not have rows in store. The script uses foreign keys that you have set up.

SAMPLE OUTPUT

	 1> sp__checkkey

	 [ for each foreign key in database ]
	 declare @cnt int
	 set nocount on
	 select title_id into #tmp from roysched
	 delete #tmp from #tmp p,titles d where p.title_id=d.title_id
	 if exists ( select * from #tmp )
	 begin
	  select @cnt=count(*) from #tmp
	  if @cnt>=100
	  print 'first 100 keys in roysched w/o data in titles'
	  else
	  print 'distinct keys in roysched w/o data in titles'
	  set rowcount 100
	  select distinct * from #tmp
	  set rowcount 0
	 end
	 drop table #tmp
	 go
	 [ WHEN RUN THIS SCRIPT PRODUCES ]

	 1> [ execute above ]
	 distinct keys in roysched w/o data in titles

	 title_id
	 ----------
	 A12224

NAME

sp__colconflict - Analyze conflicting columns definitions in current database

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Reports column with multiple definitions (for example, one. defined in table A as an int and in B as a smallint).

USAGE

sp__colconflict [ @objectname ]

@objname specifies objects to select (query like "%@objname%")

SEE ALSO

sp__helpcolumn, sp__collist

sp__helpnull

SAMPLE OUTPUT

 1> exec sp__colconflict
 Column               Table                Defn            Null
 -------------------- -------------------- --------------- --------
 attribute            schedule_history     char(30)        null
 attribute            schedule_attributes  varchar(127)    null
 description          error_severity       char(18)        null
 description          system               char(255)       null
 description          disks                char(30)        null
 description          user_view            char(30)        null
 description          remarks              varchar(127)    null
 description          hardware             varchar(127)    null
 group_name           schedule_defn        char(18)        not null
 group_name           schedule_groups      char(18)        not null
 group_name           comn_sysusers        char(30)        not null
 group_name           model                char(30)        null

NAME

sp__collist - Analyze columns in current database

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Reports column definitionsand reports columns with multiple definitions (for example, one. defined in table A as an int and in B as a smallint) and columns that have conflicting "allow null" definitions.

USAGE

sp__collist [ @objectname ]

@objname specifies objects to select (query like "%@objname%")

SEE ALSO

sp__helpcolumn

SAMPLE OUTPUT

  1> exec sp__collist
  column          type            Nulls    Ident    Num Tables
  --------------- --------------- -------- -------- ----------
  action          char(18)        null              1 Tables
  allow_null      tinyint         null              1 Tables
  allow_updates   smallint        null              1 Tables
  attribute       char(30)        null              1 Tables
  attribute       varchar(127)    null              1 Tables
  audit_trail     tinyint         not null          1 Tables
  benchmark       int             null              4 Tables

NAME

sp__colnull - columns with conflicting nullity

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Reports column with same name but conflicting nullity (for example, one. defined in table A as allow null and in B as not allow null).

USAGE

sp__colnull [ @objectname ]

@objname specifies objects to select (query like "%@objname%")

SEE ALSO

sp__helpcolumn, sp__collist,

sp__helpconflict

SAMPLE OUTPUT

 1> exec sp__colnull
 Column               Table                Defn            Null
 -------------------- -------------------- --------------- --------
 controller           disk_usg             char(10)        not null
 controller           controllers          char(10)        not null
 controller           disks                char(10)        null
 crdate               audit_trail          datetime        not null
 crdate               db_space_save        datetime        not null
 crdate               comn_sysobjects      datetime        not null
 crdate               server_syslocks      datetime        not null
 crdate               db_space_history     datetime        not null
 crdate               password_history     datetime        not null
 crdate               table_space_save     datetime        not null
 crdate               table_space_history  datetime        not null
 crdate               alerts               datetime        null
 crdate               comn_database        datetime        null

NAME

sp__configure - a better system configuration viewer

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

This, unlike sp_configure, gives straight result set viewing, sorted into categories. It also shows any defaults if available.

SEE ALSO

USAGE

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

SAMPLE OUTPUT

 Category                     Option Name               Value   Default
 ---------------------------- ------------------------- ------- -------
 default data cache           2K I/O Buffer Pool        7800    NULL
 SQL Server Administration    Upgrade version           11510   1100
 default data cache           User Defined Cache        0       0
 default data cache           User Defined Cache        7800    NULL
 Physical Memory              additional netmem         0
 Languages                    default character set ID  2       1
 Languages                    default language          0

NAME

sp__date - Show date conversion formats for the server

AUTHOR

Simon Walker, The SQL Workshop LTD.

DESCRIPTION

show date conversion formats for server. you could look it up but...

USAGE

sp__date [ @datestring ]

@datestring is date string to convert - if not given uses getdate()

SAMPLE OUTPUT

1> exec sp__date

  0   Oct 31 1997 12:21AM           100  Oct 31 1997 12:21AM
  1   10/31/97                      101  10/31/1997
  2   97.10.31                      102  1997.10.31
  3   31/10/97                      103  31/10/1997
  4   31.10.97                      104  31.10.1997
  5   31-10-97                      105  31-10-1997
  6   31 Oct 97                     106  31 Oct 1997
  7   Oct 31, 97                    107  Oct 31, 1997
  8   00:21:35                      108  00:21:35
  9   Oct 31 1997 12:21:35:150AM    109  Oct 31 1997 12:21:35:150AM
  10  10-31-97                      110  10-31-1997
  11  97/10/31                      111  1997/10/31
  12  971031                        112  19971031

NAME

sp__datediff - Gives real datediff between time and now

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

ARGUMENTS

        @starttime datetime
        @scale char(1)
        @outp float     output

DESCRIPTION

Returns time differences in FLOATING format. So difference between 3:20AM and 4:40AM in hours is 1.333 and in days is 1.333/24 or about .055.

 if     @scale='h'
        select @outp= convert(float,datediff(mi,@startdate,getdate()))/60
 else   if @scale='d'
        select @outp= convert(float,datediff(hh,@startdate,getdate()))/24
 else   if @scale='m'
        select @outp= convert(float,datediff(ss,@startdate,getdate()))/60
 else   if @scale='s'
        select @outp= convert(float,datediff(ss,@startdate,getdate()))

SAMPLE OUTPUT

 1> declare @x float
 2> exec sp__datediff "Jan 20 1999","h",@x

NAME

sp__dbspace - Show current db space

AUTHOR

Unknown

DESCRIPTION

calculates out amounts reserved and used for current database

SEE ALSO

sp__qspace

SAMPLE OUTPUT

 1> sp__dbspace
 1> exec sp__dbspace
 Name      Data MB    Used MB     Percent Log MB  Log Used  Log Pct
 --------- ---------- ----------- ------- ------- --------- -------
 statsdb           10         6.6   66.29       0      0.00    0.00

NAME

sp__dbuse - Show system information

AUTHOR

Philippe Wathelet (Flexible Consulting) philipew@hotmail.com )

VERSION

5.2

DESCRIPTION

This procedure gives a complete outlook of a database relating to its various components, including the server it runs on. It is designed to operate in a System 11 environment only.

USAGE

 ......:1>sp__dbuse <database name>
 ......:2>go

From any database including . If only the beginning of the DB name is given then the full name is found if identifiable.

OR

 ......:1>sp__dbuse
 ......:2>go

defaults to the current DB. This usage will return the most information.

OR

 master:1>sp__dbuse
 master:2>go

If the current DB is master, defaults to the DB with the log full else to the first DB with a blocking lock on else to the DB with the largest data percentage full.

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'

NAME

sp__depends - It's a superset of sp_depends.

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

NAME

sp__diskdevice - List disk devices and their basic information

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Basic information about disk devices

USAGE

sp__diskdevice [@devname char(30)=NULL, @dont_format char(1)=null, @sortorder char(1)='N' ]

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

@dont_format: standard argument if set then print full column width

@sort_order: default to "N" which is to sort files by dbname. If anything else is passed, it will sort by filename

on sql server you can run

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

SAMPLE SQL SERVER OUTPUT

 1> exec sp__diskdevice

NAME

sp__dumpdevice - List dump devices and their basic information

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

NAME

sp__find_missing_index - Lists potentially missing indexes

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

NAME

sp__flowchart - List execution flow of procedures

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

NAME

sp__grep - grep for Sybase SQL Server System 10

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}'

NAME

sp__groupprotect - Synopsis of protection stuff.

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

NAME

sp__help - Modified sp_help

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> 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

NAME

sp__helpcolumn - List columns for table / database

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
 ...

NAME

sp__helpdb - shows database information in a nice format

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

NAME

sp__helpdbdev - Show how database uses devices

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

NAME

sp__helpdefault - list default information in current databases

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
  -------------------- ---------- -------------------------------------

NAME

sp__helpdevice - Break down database devices into a nice report

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

NAME

sp__helpgroup - Lists groups members in current database (incl. aliases)

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.

NAME

sp__helpindex - Shows indexes by table

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

 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

NAME

sp__helplogin - Show logins and remote logins to the server

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

NAME

sp__helpmirror - Show mirrors, discover broken mirrors

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 *******

NAME

sp__helpobject - list objects in current databases

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 Usd Definition
 ------------- --------- ------------------------------------------
 pub_idrule    1         @pub_id in ("1389", "0736", "0877", "1622"
 title_idrule  2         @title_id like "[0-9][0-9][0-9][0-9]"

 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

NAME

sp__helpproc - list procedure information in current databases

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

NAME

sp__helprotect - Protection Information for current database

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

NAME

sp__helprule - list rule information in current databases

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]"

NAME

sp__helpsegment - Stored procedure to check segments on server

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

migrator 7 LDS datadevice 10.00 statsdb 99 U DS datadevice 2.00

NAME

sp__helptable - list table information in current databases

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

NAME

sp__helptext - show helptext info with spacing correct

AUTHOR

Andrew Zanevsky, AZ Databases, Inc.

DESCRIPTION

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

USAGE

sp__helptext @objectname

NAME

sp__helptrigger - list trigger information in current databases

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

NAME

sp__helptype - get data types (user and system defined)

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

NAME

sp__helpuser - Lists users in current database by access level (incl. aliases)

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

NAME

sp__helpview - list views in current databases

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
  -------------------- ---------- --------------------------------------

NAME

sp__id - Tells you who you are and in which database

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

NAME

sp__indexspace - Space breakdown by index in current database

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

NAME

sp__iostat - Equivalent of the unix iostat command

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

NAME

sp__isactive - Check who is doing something on server

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

NAME

sp__lock - Check locks on server

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

object is not in the current database, it will be displayed

NAME

sp__lockt - Check table locks on server

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

object is not in the current database, object id will be displayed

NAME

sp__ls - Equivalent of the unix ls command

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
 ...

NAME

sp__noindex - Analysis of what tables lack indexes.

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

NAME

sp__objprotect - Synopsis of protection stuff.

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

NAME

sp__proclib_version - Quick dump of statistics on server

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Returns Extended Stored Procedure Library version number

SAMPLE OUTPUT

 1> exec sp__proclib_version

 -----
  3.80

NAME

sp__qspace - a faster version of sp__dbspace

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

NAME

sp__quickstats - Quick dump of statistics on server

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

NAME

sp__read_write - Identifies which tables are read & which are written by procs

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

NAME

sp__revalias - Reverse engineer aliases

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'

NAME

sp__revbindings - reverse engineer bindings in db

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

NAME

sp__revdb - Reverse engineer database layout of the server

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

NAME

sp__revdevice - Reverse engineer device layout of the server

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

NAME

sp__revgroup - Reverse engineer groups layout of the server / database

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'

NAME

sp__revindex - Reverse engineer indexes in current database

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__revindex

 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

NAME

sp__revkey - Reverse engineer keys in the database

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

NAME

sp__revlogin - Reverse engineer logins for current server

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

in with "N.A." instead of the actual password.

NAME

sp__revmirror - Reverse engineer mirrors on current server

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'

NAME

sp__revrole - reverse engineer role granting sql for this server

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

NAME

sp__revrule - Reverse engineer rules in the database

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'
 exec sp_bindrule 'cc2_sd_product_group', 'sd_product_group.int'
 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'

NAME

sp__revsegment - Reverse engineer segments of current server / database

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'

NAME

sp__revtable - Reverse engineer tables of current server / database

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
 )

NAME

sp__revtype - Reverse engineer types in database

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"

NAME

sp__revuser - Reverse engineer users of current server / database

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'

NAME

sp__segment - Segment information

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

NAME

sp__server - Summary info about a server

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/sybase/datadev.dat     20.0MB   20.0MB    0.0MB
 datadev2       /disk1/sybase/datadev2.dat    19.5MB   17.0MB    2.5MB
 datadev3       /disk1/sybase/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

NAME

sp__size - rewrite

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

SEE ALSO

USAGE

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

(return status = 0)

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             180KB         -52          73         182
 sp_dboption              116KB          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)

NAME

sp__stat - Sybase iostat/vmstat equivalent for server

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

NAME

sp__syntax - Syntax of a stored procedure

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)

NAME

sp__trigger - Useful synopsis report of current database trigger schema

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    .................. ............... ...............

NAME

sp__uptime - shows server uptime

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

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

NAME

sp__vdevno - Show used device numbers

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

NAME

sp__who - Check who is on server

AUTHOR

Simon Walker, The SQL Workshop LTD. Ed Barlow

DESCRIPTION

pretty version of sp_who

USAGE

sp__who [ @parameter ]

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

SAMPLE OUTPUT

 1> exec sp__who

 spid logi   host     proc dbname     status   cmd              bk
 ---- ------ -------- ---- ---------- -------- ---------------- --
 1    sa     gamesrus 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

1> exec sp__who MyDb

spid logi hostinfo proc dbname status cmd bk

 ---- --------- --------         ----  ----------  -------- ---------------- --

15 gemalarms unix_monitor.pl 13299 gemalarms recv sle AWAITING COMMAN 0 20 gemalarms sybmon filter.pl 3784 gemalarms recv sle AWAITING COMMAN 0 31 gemalarms sybmon port_monitor.pl gemalarms recv sle AWAITING COMMAN 0 38 gemalarms monitor_appslog. 4236 gemalarms recv sle AWAITING COMMAN 0

NAME

sp__whoactive - Check who is doing something on server

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.)

NAME

sp__whodo - Check who is doing something on server

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

NAME

sp__whoe - Long format Who is on System

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