EXTENDED STORED PROCEDURES

The Complete GEM Documentation Slide Show: What Is GEM Download GEM

An Introduction To GEM How To Install Getting Started Guide

Batch Jobs Overview GEM Screenshots Server Backup and Maintenance

Utilities Guide The GEM Console New System Stored Procedures

FAQ and Change Log Common Perl Libraries Pricing & Right To Use

Stored procedure library

The following is a guide to a FREE set of stored procedures that extend the Sybase and Microsoft SQL Server provided system procedures. These procedures provide additional functionality and new ways to look at the data provided in the System Tables. They also provide new formats that allows users, developers, and administrators to see the data they want in the format they want. This collection has been developed by several authors, and is maintained by Edward Barlow. This package is intended to be a professional tool suitable for general use on all your servers.

These procedures are installed into sybsystemprocs on Sybase and into master on Microsoft Sql Server. All procedure names start with "sp__" (two underscores). This naming convention ensures that the procedures, when run, will be available from any database and that, when they are run, the database context will be the current database. This naming convention also ensures that there are no naming conflicts with Sybase's internal procedures. Thus, if you want to see space in the statsdb database, you can run:

  use statsdb
  exec sp__dbspace

Most of these procedures can be passed the parameter @dont_format='Y' to produce unformatted output. One goal was to produce output that is readable in 80 column mode for convenience in isql/osql. This is done by truncating some fields (dbname, username etc), which can result in truncated output. If you need more detail or are using a graphical query analyzer you can pass the @dont_format='y' argument and it will show all fields at full length (no column truncation).

   sp__who @dont_format='Y'

Extended Stored Procedure Library Links. Home Page Standalone Download GEM Download

These stored procedures are released as Free software under a standard GPL license.

These procedures have been tested under

  SQL SERVER 2000
  SQL SERVER 2005
  SYBASE 4.9
  SYBASE 10
  SYBASE 11
  SYBASE 12
  SYBASE 15

Installation

The library is distributed in two ways. The Generic Enterprise Manager software incldues a copy and a full featured user interface to install the procedures. GEM also uses these procedures extensively - the console creates numerous reports based on these procedures that can be viewed using your web browser. You can also download these procedures as a standalone package.

The primary interface to install this library is to use the configure.pl program. configure.pl is a perl script that requires DBI and either DBD::Sybase (unix) or DBD::ODBC (windows). You can also use the older - unsupported .bat/.sh files that are shipped with the distribution but let me repeat - they are not supported. The .bat files require two parameters SERVERNAME and PASSWORD (the installSQLSVR.bat script only requires SERVERNEAME - it uses native authentication).

To run configure.pl type

  perl configure.pl -?

which should respond with

   Unknown option: ?
   usage: configure.pl --DEBUG --USER=sa --PASSWORD=pass --SERVER=svr --MINVERSION=ver --UPGRADE_TO_LATEST --ALLSERVERS|--SQLSERVERS|--SYBSERVERS [-FILE=File]

If it does not produce the above output on windows - and the cause is missing libraries (the above mentioned DBI and DBD::ODBC), you can install them by running

  ppm install DBI
  ppm install DBD::ODBC

If the libaries are missing on unix, contact your unix administrator.

If the above command works (ie it displays the command syntax) - your perl has all necessary modules installed - you can just run

   perl configure.pl

ignoring the command line arguments and it will ask you for SERVER/LOGIN/PASSWORD. The login you use must have sa_role (sybase) or sysadmin (sql server) role.

As system databases will be modified by these procedures, it is suggested that you dump that database before you load the procedure.

If you are not able to get the perl installer working - you can install via the following unspported scripts:

 configure.sh - the original shell script installer
 installSQLSVR.bat
 installSYBASE12.bat
 installSYBASE15.bat
 installSYBASE_MDA.bat - must change to mda procedure directory

You are, of course, required to know the sa password to the server to install. Well thats not quite true - sql server, if you enter an incorrect password, will attempt to use native windows authentication (so the install will work if you have admin role on the server). Note that, on sybase, the default size for sybsystemprocs is a bit small - it is recommended you extend the default size to use the full sysprocsdev device that gets allocated during installation. You could always just try an install and if you get out of space messages - to extend the space and reinstall.

Special thanks

Special thanks to Simon Walker of the SQL Workshop Ltd, who authored many of them and who greatly assisted in this project. Special thanks to Desiree Johnson for her help. The menu.sh program is compliments of Lars Karlsson of Sweden. The permanent home for this distribution is http://www.edbarlow.com

Right to use, resale and copyright

All procedures in this package are copyright (c) 1994-2007 by Edward Barlow. They are released under a standard GPL license agreement. The license will be changed to GPL v3 when the GPL v3 draft is formalized.

You may redistribute the package at will (see below). Tell your friends. Give me access to procedures that you have written for future versions. Tell me about bugs. Be nice - I am making no money off this.

You are allowed to use this software so long as all copyright notices, README, and other documentation are not altered and so long as no money is made by the sale of this software (i.e. you cant include it in a commercial package without permission). If you would like to "make money" or include the code in a commercial package, I ask that you decide on a "fair" price and create some form of "fair" agreement. Make two copies, sign them both, and send them to the package author (Edward Barlow). If the agreement seems fair, I will sign both and send one copy back to you, and we will have a deal. I have put significant effort into this code and, while my primary purpose is to create software for people to use, I expect a fair shake from anybody who can profit from my endeavors.

Procedures submitted by outside authors retain the authors copyright (which can be found in the code files and which should be noted in this documentation) but I (Edward Barlow and any companies I create to distribute software) get the right to redistribute them as i see fit (this is to protect me).

Uninstall

SQL statements to uninstall this package are contained in the file cleanup.SQL.

Warranty

(Are you kidding...) This software is provided as-is. No warranties or guarantees are made. To the best of my knowledge, any bugs or outstanding issues are documented in the file "BUGS" that comes with the source code.

That file is probably NOT shipped in this distribution because there are no no bugs - although there is generally a bunch of changes that get made whenever the Database vendor releases a new version of the database. If you notify me of any bugs, i will fix them as i can.

List of procedures

A menu program that provides a shell level interface is available, thanks to Lars Karlsson of Sweden, in the program menu.sh. Run it and see!

GENERAL HELP PROCEDURES

sp__depends Better version of sp_depends
sp__help Better sp_help
sp__helpdb Database Information
sp__helpdevice Break down database devices into a nice report
sp__helpgroup List groups in database by access level
sp__helpindex Shows indexes by table
sp__helpsegment Segment Information
sp__helprotect Simple Protection Info for the database
sp__helptext Show comments with line splits ok
sp__helpuser Lists users in current database by group (includes aliases)
sp__lock Lock information
sp__syntax Works on any procedure to give you syntax
sp__who sp_who that fits on a page

SYSTEM ADMINISTRATOR PROCEDURES

sp__block Blocking processes.
sp__dbspace Summary of current database space information.
sp__dumpdevice Listing of Dump devices
sp__diskdevice Listing of Disk devices
sp__helpdbdev Show how Databases use Devices
sp__helplogin Show logins and remote logins to server
sp__helpmirror Shows mirror information, discover broken mirrors
sp__segment Segment Information
sp__server Server summary report (very useful)
sp__stat Give basic server performance information (loops)
sp__vdevno Who's who in the device world

DBA PROCEDURES

sp__badindex list badly formed indexes (allow nulls) or those needing statistics
sp__collist list all columns in database
sp__find_missing_index Finds keys that do not have associated index
sp__flowchart Makes a flowchart of procedure nesting
sp__groupprotect Permission info by group
sp__indexspace Space used by indexes in database
sp__id Gives information on who you are and which db you are in
sp__noindex list of tables without indexes.
sp__helpcolumn show columns for given table
sp__helpdefault list defaults (part of objectlist)
sp__helpobject list objects
sp__helpproc list procs (part of objectlist)
sp__helprule list rules (part of objectlist)
sp__helptable list tables (part of objectlist)
sp__helptrigger list triggers (part of objectlist)
sp__helpview list views (part of objectlist)
sp__objprotect Permission info by object
sp__read_write list tables by # procs that read, # that write, # that do both
sp__trigger Useful synopsis report of current database trigger schema
sp__whodo sp__who - filtered for only active processes

AUDIT PROCEDURES

sp__auditsecurity Security Audit On Server
sp__auditdb Audit Current Database For Potential Problems
sp__checkkey Generate script for referential integrity problems (uses key info from sp_foreignkey)

REVERSE ENGINEERING PROCEDURES

sp__revalias get alias generation script for current database
sp__revdb get database generation script for server
sp__revdevice get device generation script for server
sp__revgroup get group generation script for current database
sp__revindex get index generation script for current database
sp__revlogin get login generation script for server
sp__revmirror get mirror generation script for current database
sp__revsegment get segment generation script for current database
sp__revtable get table generation script for current database
sp__revuser get user generation script for current database

OTHER PROCEDURES

sp__bcp Create unix script to bcp in/out database
sp__date Who can remember all the date styles?
sp__iostat Loops n times showing active processes only
sp__grep Search for patern
sp__isactive Shows info about a single active process
sp__ls Lists specific objects
sp__quickstats Quick dump of server summary information
sp__whoactive Show info about who is active

Auditing error codes

The following is a list of errors generated by the auditing procedures

   31001 User +name+ Is Locked
   31002 Login +name+ Is Expired
   31003 User +name+ Has Null Password
   31003 User +name+ Has Null/Short Password
   31003 User +name+ Has Short (<=4 character) Password
   31004 User +name+ Has +dbname+ Database As Default
   31005 Allow Updates is Set
   31006 ERROR: Num Open Devices Parameter Set Too Low
   31007 ERROR: Num Open Databases Parameter Set Too Low
   31008 Allow Updates is Set
   31009 User +name+ Has Pasword=Id
   31010 Allow Updates is Set
   31011 Tempdb is only +@tempdb)+MB
   31012 User sa is trusted from +srvname
   31013 Database +name+ Created For Load
   31014 Database +name+ Suspect
   31015 Database +name+ Offline
   31016 Database +name+ Offline until recovery completes
   31017 Database +name+ Is Being Recovered
   31018 Database +name+ Has Suspect Pages
   31019 Database +name+ Is Being Upgraded
   31020 Database +name+ -> No Log Device and No TL on Chkpt
   31021 ERROR: MIRROR BROKEN: +name
   31022 ERROR: CHECK MIRRORING: +name
   31025 x.name+ Config Has Been Reset To +c.value2+ (default=+c.defvalue)
   31030 ERROR: Login +name+ Has an invalid default db (dbname)
   31031 Device +rtrim(dev.name)+ Is Mapped But Unused (no segments). Size (MB) = usg.size/512.
   31032 ERROR: Sybsystemprocs should be > 60 MB (@c)
   31033 ERROR: tempdb should be >= 100 MB (@c)
   31034 Database +name+ Is not auto-shrink
   31035 Database +name+ Has No Torn Page Detection
   31036 Database +name+ Is Unusable
   31037 User +name+ Has Stupid Password
   31038 user +u.name+ can use db but lacks login
   31040 File +name+ Is Not Autogrowth
   31101 User +user_name(uid)+ Owns Objects
   32000 Login +m.name+ is aliased to +u.name
   32007 suid + a.suid+ lacks login and is aliased to +u.name
   32008 user +u.name+ can use db but lacks login suid=suid
   32009 database +db_name()+ has not been tran dumped in
   32010 Object +name+ Has No Comments
   32010 count(*)+ Objects Have No Comments
   32011 Comments for id + id + have no object
   32013 v.name + Permission granted to + u.name
   32014 Table +object_name(id)+ Index + name + is suspect
   32016 Object: +object_name(s1.id)+ Has Statistics From +s1.moddate 0)+ and +s2.moddate 0)
   32017 Object: +object_name(s1.id)+ Has OLD Statistics From +s1.moddate 0)
   32018 name + permissions granted to public on +object_name(id) from sysprotects master.dbo.spt_values c
   32019 db_name()+ has +@dat+MB data and + @log+MB log
   32100 Login + m.name + is aliased to + u.name
   32101 User +user_name(uid)+ Has + count(*)+ Objects
   32101 User +user_name(uid)+ Owns Objects
   32102 Object +name+ exists in the db and in sybsystemprocs - This is a possible Trojan Horse
   32102 Object +name+ in master - Possible Trojan Horse
   32102 Object +o.name+ Exists in master and the database - Possible Trojan Horse
   32102 Object +o.name+ Possible Trojan Horse (Exists in master)
   32103 Object +object_name(id)+ has access to syslogins
   32104 No Groups Exist In Database +db_name()
   32105 User +n1.name+ is a member of group public
   32106 Group Public +rtrim(v.name)+ access to + count(*) + objects
   32106 Group Public access to object +o.name+ type=+o.type
   32110 DB Collation +collation+ != Server Collation + serverproperty(collation)

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.

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

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.

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)

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

sp__bcp

Produce object

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

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

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

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

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%")

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

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%")

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

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%")

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

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.

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

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

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

sp__dbspace

Show current db space

AUTHOR

Unknown

DESCRIPTION

calculates out amounts reserved and used for current database

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

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.

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

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

 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.

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'

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

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]

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

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

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

SAMPLE OUTPUT

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

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

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

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

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.

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

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)

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

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.

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

sp__helpdb

shows database information in a nice format

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

Show standard database information in a concise format

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

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.

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

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.

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

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.

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

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.

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.

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

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 ]

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

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

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 ]

BUGS

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

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 ]

BUGS

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

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.

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

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 ]

BUGS

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

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

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 ]

BUGS

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

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__helptrigger

list trigger information in current databases

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

list trigger information in current database

USAGE

sp__helptrigger [ @objectname ]

BUGS

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

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

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.

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

sp__helpview

list views in current databases

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

list views information in current database

USAGE

sp__helpview [ @objectname ]

BUGS

SAMPLE OUTPUT

 1> sp__helpview

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

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

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 ]

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

sp__iostat

Equivalent of the unix iostat command

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

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

sp__isactive

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

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

SAMPLE OUTPUT

 1> exec sp__lock

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

sp__lockt

Check table locks on server

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

pretty print table locks

USAGE

sp__lockt

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

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

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

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.

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

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

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.

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

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

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

sp__revalias

Reverse engineer aliases

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

reverse engineers aliases

USAGE

sp__revalias

SAMPLE OUTPUT

 1> sp__revalias

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

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

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

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

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

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

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

SAMPLE OUTPUT

 1> sp__revgroup

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

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

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

sp__revkey

Reverse engineer keys in the database

AUTHOR

Created By : "Chris Vilsack"

DESCRIPTION

reverse engineers keys in your current database.

USAGE

sp__revkey

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

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

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

sp__revmirror

Reverse engineer mirrors on current server

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

reverse engineers mirror information

USAGE

sp__revuser

SAMPLE OUTPUT

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

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

sp__revrule

Reverse engineer rules in the database

AUTHOR

Created By : "Chris Vilsack"

DESCRIPTION

reverse engineers rules in your current database.

USAGE

sp__revrule

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'

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

SAMPLE OUTPUT

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

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

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
 )

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

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"

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

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'

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

sp__server

Summary info about a server

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

server summary report

USAGE

sp__server

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

sp__size

rewrite

AUTHOR

Edward Barlow ( SQL Technologies, inc. )

DESCRIPTION

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)

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

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)

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]

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

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

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

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

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

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

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

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

sp__monbackup

show last backup information

AUTHOR

Edward Barlow (SQL Technologies)

DESCRIPTION

Shows information about database backups from the mda tables. This lists the last time each database was backed up, whether the backup failed, and if the backup is running.

USAGE

SAMPLE OUTPUT

 DBName                         BackupStartTime     BkpFail     InProgress
 ------------------------------ ------------------- ----------- -----------
 master                         Apr  8 2006  5:04AM           0           0
 sybsystemdb                                   NULL           0           0
 model                                         NULL           0           0
 tempdb                         Apr 10 2006  8:38AM           0           0
 sybsystemprocs                 Apr 10 2006  8:38AM           0           0
 clientabc                      Apr 10 2006  8:38AM           0           0
 shared1                        Apr 10 2006  8:37AM           0           0
 archive1                       Apr 10 2006  8:37AM           0           0
 equityrisksys                  Apr 10 2006  8:37AM           0           0
 imagvue_test                   Apr 10 2006  8:37AM           0           0

sp__moncache

show cache information

AUTHOR

Edward Barlow (SQL Technologies)

DESCRIPTION

Shows a summary caching for the server by cache.

This procedure uses vmstat/iostat format - you can run it either once or in a loop (@num_iter times with @num_sec_delay seconds between runs).

USAGE

sp__moncache @num_sec_delay, @num_iter, @dont_format

 - @num_sec_delay : int    : seconds delay between output runs
 - @num_iter      : int    : number of iterations
 - @dont_format   : char(1): no output formating if its not null

SAMPLE OUTPUT

1> sp__moncache

 CacheName                      PhysicalReads PhysicalWrites LogicalReads HitRatio
 ------------------------------ ------------- -------------- ------------ -------------
 default data cache                   6412018           2486    982314677          99.3

1> sp__moncache 3,3

 CacheName                      PhysicalReads PhysicalWrites LogicalReads HitRatio
 ------------------------------ ------------- -------------- ------------ -------------
 default data cache                       129              0      1296782          99.3
 CacheName                      PhysicalReads PhysicalWrites LogicalReads HitRatio
 ------------------------------ ------------- -------------- ------------ -------------
 default data cache                       306              0      1083561          99.3
 CacheName                      PhysicalReads PhysicalWrites LogicalReads HitRatio
 ------------------------------ ------------- -------------- ------------ -------------
 default data cache                       603              0       991181          99.3

sp__mondump

quick dump of mda table information

AUTHOR

Edward Barlow (SQL Technologies)

DESCRIPTION

MDA table dump. This dumps the current server state - an composite report of everything going on at the current time. The purpose of this procedure is for post-mortems of problems on production systems where you do not have time to actually figure out why there is a problem because you must fix that problem immediately. Run sp__mondump, fix the problem, and then look at the output later to diagnose.

SHows only open objects and recent sql

USAGE

exec sp__mondump

SAMPLE OUTPUT

extensive output... run it and see

sp__monengine

show engine usage from MDA tables

AUTHOR

Edward Barlow (SQL Technologies)

DESCRIPTION

Summary of Engine usage. Run once, the proc does NOT show a breakdown of %usage and %idle because these numbers are only available since server start - which of course is completely useless.

This procedure uses vmstat/iostat format - you can run it either once or in a loop (@num_iter times with @num_sec_delay seconds between runs).

USAGE

sp__monengine @num_sec_delay, @num_iter, @dont_format

 - @num_sec_delay : int    : seconds delay between output runs
 - @num_iter      : int    : number of iterations
 - @dont_format   : char(1): no output formating if its not null

SAMPLE OUTPUT

1> sp__monengine

 EngineNumber AffinitiedToCPU Status               Connections
 ------------ --------------- -------------------- -----------
            0               0 online                        30
            1               1 online                        29
            2               2 online                        31
            3               3 online                        28
            4               4 online                        28

1> sp__monengine 3,3

 Engine AffinitiedToCPU Status     Connections Sys Usr Idle
 ------ --------------- ---------- ----------- --- --- ----
      0               0 online              30  0% 50% 50%
      1               1 online              29  0%  0% 75%
      2               2 online              31  0%  0% 75%
      3               3 online              28  0% 33% **%
      4               4 online              28  0%  0% 75%

 Engine AffinitiedToCPU Status     Connections Sys Usr Idle
 ------ --------------- ---------- ----------- --- --- ----
      0               0 online              30  0%  0% 75%
      1               1 online              29  0%  0% 75%
      2               2 online              31  0%  0% 60%
      3               3 online              28  0%  0% 50%
      4               4 online              28  0%  0% 75%

 Engine AffinitiedToCPU Status     Connections Sys Usr Idle
 ------ --------------- ---------- ----------- --- --- ----
      0               0 online              30  0%  0% 75%
      1               1 online              29  0%  0% 75%
      2               2 online              31  0%  0% 75%
      3               3 online              28  0%  0% 75%
      4               4 online              28  0%  0% 60%

sp__monio

I/O usage by device

AUTHOR

Edward Barlow (SQL Technologies)

DESCRIPTION

I/O usage by device

This procedure uses vmstat/iostat format - you can run it either once or in a loop (@num_iter times with @num_sec_delay seconds between runs).

USAGE

sp__monio @num_sec_delay, @num_iter, @dont_format

 - @num_sec_delay : int    : seconds delay between output runs
 - @num_iter      : int    : number of iterations
 - @dont_format   : char(1): no output formating if its not null

SAMPLE OUTPUT

1> sp__monio

 LogicalName                    Reads       Writes      IOTime
 ------------------------------ ----------- ----------- -----------
 data_device_4                        95381         294    35558600
 log_device_1                         53243      603545    85067100
 master                                2048       33741    36658200
 sysprocsdev                           1928          80      246900
 tempdb_ufs_device_1                1189478     7810569   260407600

1> sp__monio 3,3

 LogicalName                    Reads       Writes      IOTime
 ------------------------------ ----------- ----------- -----------
 data_device_4                            0           0           0
 log_device_1                             0          13           0
 master                                   0          21         500
 sysprocsdev                              0           0           0
 tempdb_ufs_device_1                      0         232           0

 LogicalName                    Reads       Writes      IOTime
 ------------------------------ ----------- ----------- -----------
 data_device_4                            0           0           0
 log_device_1                             0           0           0
 master                                   0           6         100
 sysprocsdev                              0           0           0
 tempdb_ufs_device_1                      0           0           0

 LogicalName                    Reads       Writes      IOTime
 ------------------------------ ----------- ----------- -----------
 data_device_4                            0           0           0
 log_device_1                             0           0           0
 master                                   0           7           0
 sysprocsdev                              0           0           0
 tempdb_ufs_device_1                      0           0           0

sp__monlock

show engine usage from MDA tables

AUTHOR

Edward Barlow (SQL Technologies)

DESCRIPTION

Improved version of sp__lock with additional lockstate information. While that, in and of itself is not that useful, Watching the change in locks can be useful.

This procedure uses vmstat/iostat format - you can run it either once or in a loop (@num_iter times with @num_sec_delay seconds between runs).

USAGE

sp__monlock @num_sec_delay, @num_iter, @dont_format

 - @num_sec_delay : int    : seconds delay between output runs
 - @num_iter      : int    : number of iterations
 - @dont_format   : char(1): no output formating if its not null

SAMPLE OUTPUT

1> sp__monlock

 SPID   ObjName                                  LockState     LockType             LockLevel    WaitTime
 ------ ---------------------------------------- ------------- -------------------- ------------ -----------
     65 tempdb.sysstatistics                     Granted       shared intent        TABLE        NULL
     65 tempdb.#pl_month____01000650009467551    Granted       exclusive table      TABLE        NULL
    858 shared1.hts_price_env                    Granted       shared intent        TABLE        NULL
    858 clientmlp.hts_price_env                  Granted       shared intent        TABLE        NULL

1> sp__monlock 3,3

 SPID   ObjName                             Type
 ------ ----------------------------------- --------------------
     65 shared1.hts_underlying              shared page
     65 tempdb.sysstatistics                shared intent
     65 shared1.hts_underlying              shared intent
     65 tempdb.#sec_________010006500094675 exclusive table
     71 clientmlp.systabstats               shared intent
     71 clientmlp.sysstatistics             shared intent
    259 clientmlp.syscolumns                shared page
    259 clientmlp.syscolumns                shared intent
    681 clientmlp.hts_profit_loss           shared intent

 SPID   ObjName                             Type
 ------ ----------------------------------- --------------------
     65 shared1.hts_underlying              shared page
     65 tempdb.sysstatistics                shared intent
     65 shared1.hts_underlying              shared intent
     65 tempdb.#sec_________010006500094675 exclusive table
    681 clientmlp.hts_profit_loss           shared intent
   1092 master.sysmessages                  shared page
   1092 shared1.systabstats                 shared intent
   1092 shared1.sysstatistics               shared intent

 SPID   ObjName                             Type
 ------ ----------------------------------- --------------------
     65 shared1.hts_underlying              shared page
     65 tempdb.sysstatistics                shared intent
     65 shared1.hts_underlying              shared intent
     65 tempdb.#sec_________010006500094675 exclusive table
    681 clientmlp.hts_profit_loss           shared intent

sp__monlocksql

show lock information and the sql locking it

AUTHOR

Edward Barlow (SQL Technologies)

DESCRIPTION

Show information about locks AND the sql that causes them. This can be rather useful, although you need to run this from some form of windowed isql or it will scroll off and be totally unusable.

This procedure uses vmstat/iostat format - you can run it either once or in a loop (@num_iter times with @num_sec_delay seconds between runs).

USAGE

sp__monlocksql @dont_format

  - @dont_format   : char(1): no output formating if its not null

SAMPLE OUTPUT

1> sp__monlocksql

 SPID   ObjName   LockState   LockType   LockLevel   WaitTime   SQL
 481   tempdb.#L___________01004810009078207      Granted   exclusive table   TABLE      sp__monlocksql
 833   clientmlp.hts_profit_loss                  Granted   shared intent   TABLE      select hts_profit_loss.* from hts_profit_loss
 905   clientmlp.hts_profit_loss                  Granted   shared intent   TABLE      EXECUTE imagvue..swap_report_sp
 681   clientmlp.hts_profit_loss                  Granted   shared intent   TABLE      select * from hts_profit_loss where pl_date=20060407
 596   shared1.hts_yc_sp                          Granted   shared intent   TABLE      select * from hts_yc_sp where ycsp_ycsph_id = 10001685
 596   clientmlp.hts_yc_sp                        Granted   shared intent   TABLE      select * from hts_yc_sp where ycsp_ycsph_id = 10001685

sp__monnet

show network io

AUTHOR

Edward Barlow (SQL Technologies)

DESCRIPTION

Shows Network IO Usage From The Mda tables

This procedure uses vmstat/iostat format - you can run it either once or in a loop (@num_iter times with @num_sec_delay seconds between runs).

USAGE

sp__monnet @num_sec_delay, @num_iter, @dont_format

 - @num_sec_delay : int    : seconds delay between output runs
 - @num_iter      : int    : number of iterations
 - @dont_format   : char(1): no output formating if its not null

SAMPLE OUTPUT

1> sp__monnet

 PacketsSent PacketsReceived BytesSent   BytesReceived
 ----------- --------------- ----------- -------------
    36173115         2753319  1124048925     661268239

1> sp__monnet 3,3

 PacketsSent PacketsReceived BytesSent   BytesReceived
 ----------- ----------- ----------- -----------
          91         206       59726           0
 ----------- ----------- ----------- -----------
          59         201       20339           0
 ----------- ----------- ----------- -----------
         104         278       63928           0

sp__monobj

rank the most used objects in the db

AUTHOR

Edward Barlow (SQL Technologies)

DESCRIPTION

Shows Highest Usage Objects Based On Mda tables. This uses a heuristic algorithm to find what objects are most busy.

This procedure uses vmstat/iostat format - you can run it either once or in a loop (@num_iter times with @num_sec_delay seconds between runs).

USAGE

sp__monobj @num_sec_delay, @num_iter, @dont_format, @object_name

 - @num_sec_delay : int    : seconds delay between output runs
 - @num_iter      : int    : number of iterations
 - @dont_format   : char(1): no output formating if its not null
 - @object_name

Algorithm

  LogicalReads/100 + PhysicalReads/10 + PhysicalWrites +
  RowsInserted + RowsDeleted + RowsUpdated + LockRequests + Lockwaits

SAMPLE OUTPUT

1> sp__monobj

 ObjName   score
 clientmlp.hts_port_control      1633999370
 clientmlp.hts_adjustment      824849026
 shared1.hts_sec_master         611904419
 imagvue.mlp_order_summary      556344304
 shared1.hts_price_env         391646266
 clientmlp.hts_sec_master      305448783
 shared1.hts_underlying         294278940
 clientmlp.mlp_account_trader_map   286362320
 clientmlp.hts_charge_rule      242200139
 clientmlp.hts_mark            230564947
 shared1.hts_fi_sec_master      195351310
 clientmlp.hts_price_env         178223486
 shared1.hts_sm_deriv_detail   169761009
 clientmlp.hts_profit_loss      118826463
 clientmlp.hts_sm_deriv_detail   115671362
 shared1.hts_dividend_list      101212864
 shared1.hts_yc_mkt_instr_data   95322657
 risksys.RiskSecurityMaster      89780213
 clientmlp.hts_account         82741731
 clientmlp.hts_xact_history      68040909

1> sp__monobj 3,3

  ObjName                                 Op          Reads       Writes      Rows
 --------------------------------------- ----------- ----------- ----------- -----------
 clientmlp.hts_charge_rule                         0        2390           0           0
 clientmlp.hts_trigger_status                      0          23           0          23
 clientmlp.imagine_new_intra                       0          70           0          20
 clientmlp.hts_next_rowid                          0          30           0          15
 clientmlp.hts_port_control                      432         250           0          11
 clientmlp.hts_exec_adj                          318         529           0           5
 clientmlp.hts_xact_history                       42          24           0          10
 clientmlp.hts_ord_control                         0          37           0          10
 clientmlp.hts_exec_control                        0          17           0           5
 clientmlp.hts_sec_def_attribs                     0           2           0           0

 ObjName                                 Op          Reads       Writes      Rows
 --------------------------------------- ----------- ----------- ----------- -----------
 clientmlp.imagine_new_intra                       4        8588           0           0
 clientmlp.hts_exec_adj                          318         531           0           0
 clientmlp.hts_port_control                     2592         431           0           0
 sybsystemdb.syscoordinations                      5           6           0           4
 IMAGREP1_RS_RSSD.rs_locater                      13           2           0           2
 clientmlp.hts_xact_history                      180          75           0           1
 imagvue.sec                                       0           0           0           0
 shared1.one                                       0           0           0           0
 imagvue.zero                                      0           0           0           0
 shared1.zero                                      0           0           0           0

 ObjName                                 Op          Reads       Writes      Rows
 --------------------------------------- ----------- ----------- ----------- -----------
 clientmlp.hts_exec_adj                          270         445           0           0
 sybsystemdb.syscoordinations                      5           6           0           4
 clientmlp.hts_exchange_settle_rules              18           3           0           0
 imagvue.sec                                       0           0           0           0
 shared1.one                                       0           0           0           0
 imagvue.zero                                      0           0           0           0
 shared1.zero                                      0           0           0           0
 archive1.zero                                     0           0           0           0
 clientmlp.one                                     0           0           0           0
 imagvue.rules                                     0           0           0           0

sp__monopenobj

show open object info

AUTHOR

Edward Barlow (SQL Technologies)

DESCRIPTION

Shows IO on open objects From The Mda tables

This procedure uses vmstat/iostat format - you can run it either once or in a loop (@num_iter times with @num_sec_delay seconds between runs).

USAGE

sp__monopenobj @num_sec_delay, @num_iter, @dont_format, @object_name

 - @num_sec_delay : int    : seconds delay between output runs
 - @num_iter      : int    : number of iterations
 - @dont_format   : char(1): no output formating if its not null
 - @object_name

SAMPLE OUTPUT

1> sp__monopenobj

SPID ObjName PhyReads LogReads apfReads 98 clientmlp.hts_profit_loss 0 2835 0 98 tempdb.#set_portf___000009800091587 0 97780 0 381 clientmlp.hts_ord_control 0 307 0 481 master.monProcessObject 0 0 0 653 clientmlp.hts_profit_loss 0 2128 0 863 clientmlp.hts_profit_loss 0 21786 0 863 tempdb.#set_portf___000086300093785 0 375914 0 906 clientmlp.hts_profit_loss 0 24485 0 906 tempdb.#set_portf___000090600094598 0 211256 0 1126 clientmlp.hts_profit_loss 0 9556 0 1126 tempdb.#set_portf___000112600064828 0 82461 0 1163 clientmlp.hts_ord_control 270 5881 384 1163 clientmlp.hts_exec_control 6697 40707 113

sp__monpwaits

show wait information

AUTHOR

Edward Barlow (SQL Technologies)

DESCRIPTION

Shows Network IO Usage From The Mda tables

This procedure uses vmstat/iostat format - you can run it either once or in a loop (@num_iter times with @num_sec_delay seconds between runs).

USAGE

sp__monpwaits @SPID, @num_sec_delay, @num_iter, @dont_format

 - @SPID          : int    : Spid of interest
 - @num_sec_delay : int    : seconds delay between output runs
 - @num_iter      : int    : number of iterations
 - @dont_format   : char(1): no output formating if its not null

SAMPLE OUTPUT

1> sp__monpwaits

Event WaitTime Waits

 ---------------------------------------- ----------- -----------
 wait for buffer read to complete              117100       21015
 wait for someone else to finish reading       227500       57932
 waiting for semaphore                          22000           2
 waiting for incoming network data           15170900         735
 waiting for network send to complete         9033300    29420754

1> sp__monpwaits 3,3

 Event                                    WaitTime    Waits
 ---------------------------------------- ----------- -----------
 waiting for incoming network data               4600           0
 Event                                    WaitTime    Waits
 ---------------------------------------- ----------- -----------
 waiting for incoming network data               4500           0
 Event                                    WaitTime    Waits
 ---------------------------------------- ----------- -----------
 waiting for incoming network data               5100           0

sp__monrunning

show processes running > 100ms duration

AUTHOR

Mich Talebzadeh

DESCRIPTION

Show proceecures that are running for more than 100 ms

USAGE

sp__monrunning

SAMPLE OUTPUT

1> sp__monrunning

 Name                 SPID   Procedure                      Database                       Elapsed Time/ms
 -------------------- ------ ------------------------------ ------------------------------ ---------------
 kdb_trds                346 ins_imagine_intra_sp           clientmlp                                  200
 kdb_trds                346 disableTriggers_sp             clientmlp                                  450
 kdb_trds                346 get_next_rowid_rv_sp           clientmlp                                  453
 kdb_trds                346 trigger_status_sp              clientmlp                                 1013
 kdb_trds                346 addTradeFast_sp                clientmlp                                 1436
 kdb_trds                346 calcCharge_sp                  clientmlp                                 1680

sp__monserver

quick dump of mda table information

AUTHOR

Edward Barlow (SQL Technologies)

DESCRIPTION

MDA table dump. This dumps the current server state - an composite report of everything going on at the current time. The purpose of this procedure is for post-mortems of problems on production systems where you do not have time to actually figure out why there is a problem because you must fix that problem immediately. Run sp__monserver, fix the problem, and then look at the output later to diagnose.

SHows only open objects and recent sql

USAGE

exec sp__monserver

SAMPLE OUTPUT

run it and see

sp__monspid

show everything about running spid

AUTHOR

Edward Barlow (SQL Technologies)

DESCRIPTION

show everything about a running spid . Can be used to investigate locks and blocks and slow running procs etc...

USAGE

sp__monspid 765

SAMPLE OUTPUT

 SPID   SecondsConnected Login                Application
 ------ ---------------- -------------------- ------------------------
    765               36 axuv             NULL

 EngineNumber ExecutionClass EngineGroupName Priority
 ------------ -------------- --------------- -----------
            7 USERS          TOP8                      5

 SecondsWaiting BlockingSPID Wait Reason
 -------------- ------------ --------------------------------------------------

 StartTime CpuTime     MemUsageKB  PhysicalReads LogicalReads
 --------- ----------- ----------- ------------- ------------
 08:56:32            6          36             0           58

 WaitTime    PagesModified PacketsSent PacketsReceived
 ----------- ------------- ----------- ---------------
           0             0           1               0

 ID StackType           StackObject
 -- ------------------- --------------------------------------------------

 ClientHost                     ClientIP        ClientOSPID
 ------------------------------ --------------- ---------------
 jmshs                          10.5.146.12     25307

 ObjectType                     Open Object
 ------------------------------ ----------------------------------------

Recent ------------------ select * from hts_yc_sp where ycsp_ycsph_id = 10001672 union select * from shared1..hts_yc_sp where ycsp_ycsph_id = 10001672

select * from hts_yc_sp_hdr where ycsph_id = 10001673 union select * from shared1..hts_yc_sp_hdr where ycsph_id = 10001673

sp__monsql

show running sql on the server

AUTHOR

Edward Barlow (SQL Technologies)

DESCRIPTION

Shows currently executeing sql statements if no spid is passed and shows all sql history if spid is passed

USAGE

sp__monsql @spid, @dont_format

SAMPLE OUTPUT

 SPID   Time        CpuTime     PhyReads    LogReads    PagesModified Packets
   SQLText

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

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

    103           0       13753       10470       39096             0      144597
   select * from shared1..hts_sm_deriv_detail

    812           0           0           0          13             0           0
   sp__monsql

sp__montableusage

comprehensive info about a table and index usage

AUTHOR

Mich Talebzadeh

DESCRIPTION

Shows comprehensive information about how tables are used in the db

USAGE

sp__montableusage

SAMPLE OUTPUT

1> sp__montableusage

Tables accessed with Table scans ONLY, no index usage

 TableName                      Rows          Size/KB       LogicalReads PagesRead   Table scanned When last table scanned
 ------------------------------ ------------- ------------- ------------ ----------- ------------- -----------------------
 dbo.CWMM_Remote_Access                     1            16           14           2        2          Mar 30 2006  9:34AM
 dbo.monDataCache                           0             0            0           0       18          Apr 10 2006 12:41PM
 dbo.monDeviceIO                            0             0            0           0       15          Apr 10 2006  8:45AM
 dbo.monEngine                              0             0            0           0        9          Apr 10 2006 10:20AM
 dbo.monLocks                               0             0            0           0        6          Apr 10 2006  9:15AM
 dbo.monNetworkIO                           0             0            0           0       30          Apr 10 2006  8:34AM
 dbo.monOpenDatabases                       0             0            0           0        3          Apr 10 2006  8:38AM
 dbo.monOpenObjectActivity                  0             0            0           0       45          Apr 10 2006  1:29PM
 dbo.monProcess                             0             0            0           0       51          Apr 10 2006  1:09PM
 dbo.monProcessLookup                       0             0            0           0       17          Apr 10 2006 11:59AM
 dbo.monProcessObject                       0             0            0           0       22          Apr 10 2006  1:09PM
 dbo.monProcessProcedures                   0             0            0           0       17          Apr 10 2006 11:59AM
 dbo.monProcessSQLText                      0             0            0           0        6          Apr 10 2006  9:15AM
 dbo.monProcessStatement                    0             0            0           0       38          Apr 10 2006  1:09PM
 dbo.monProcessWaits                        0             0            0           0       16          Apr 10 2006  9:30AM
 dbo.monSysSQLText                          0             0            0           0       19          Apr 10 2006 11:59AM
 dbo.monSysStatement                        0             0            0           0        4          Apr 10 2006  1:00PM
 dbo.monWaitEventInfo                       0             0            0           0       27          Apr 10 2006 11:59AM

Displaying dormant tables with no DML activity, table scan or index usage

 TableName                      Rows          Size/KB       LogicalReads LockRequests
 ------------------------------ ------------- ------------- ------------ ------------

Indexes never selected or used by the optimizer

 TableName                      IndexName                      IndexSize/KB Selected    Used
 ------------------------------ ------------------------------ ------------ ----------- -----------
 dbo.PERL_DBD_TEST              PERL_DBD_T_COL_A_5530499751              32           0           0
 dbo.spt_ijdbc_mda              spt_ijdbc_mda_ind                        16           0           0
 dbo.spt_jtext                  spt_jtext_mdinfo_5440019382              16           0           0
 dbo.spt_mda                    spt_mda_ind                              16           0           0

Displaying tables with DML activity

 TableName                      Rows          Size/KB       Inserted    Updated     Deleted     LockRequests SUM DML ACTIVITY/ROWS
 ------------------------------ ------------- ------------- ----------- ----------- ----------- ------------ ---------------------------
 dbo.syblicenseslog                      1128            32          11           0           0           23 0.0097517

Tables accessed with Table scans and index usage as well

 TableName                      Rows          Size/KB       LogicalReads PagesRead   Table Scans Index Usage IndexUsage/TableScan
 ------------------------------ ------------- ------------- ------------ ----------- ----------- ----------- --------------------
 dbo.spt_values                          1745            94       150647         870      496        6191        12.48

Index usage analysis

 TableName                      IndexName                      IndexSize/KB Selected    Used        Used/SUM(Used)
 ------------------------------ ------------------------------ ------------ ----------- ----------- --------------
 dbo.spt_values                 spt_valuesclust                          94        2388        6191           1.0

sp__montopn

show top processes in use ordered by cputim

AUTHOR

Mich Talebzadeh

DESCRIPTION

Top processes in use ordered by cputime

USAGE

sp__montopn @spid

SAMPLE OUTPUT

1> sp__montopn

Process Login Application Command DBName EngineNumber MemUsageKB CpuTime WaitTime ObjectType ObjectName

   Index ID    Logical Reads Physical Reads Physical APF Reads
 ------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------ ----------- ----------- ----------- ------------------------------ ------------------------------
   ----------- ------------- -------------- ------------------

696 kdb_trds StartImagineTradesClientServer INSERT clientmlp 9 36 556 0 user index hts_ord_control

             2        129838              0                  0

658 yuxinli NULL SELECT clientmlp 5 20 17770 300 user clustered index #set_portf___00006580010838836

             1       1133153              0                  0

658 yuxinli NULL SELECT clientmlp 5 20 17770 300 user index hts_profit_loss

             4         26337              0                  0

sp__monunusedindex

show indexes that have not been used since server start

AUTHOR

Edward Barlow (SQL Technologies)

DESCRIPTION

USAGE

sp__monunusedindex @dont_format, @no_print

SAMPLE OUTPUT

   INDEX KEY:     c = clustered            u = unique
                  a = allow dup row        s = suspect
                  i = ignore dup key

 Name                                                          c u i a s List of Index Keys
 ------------------------------------------------------------- - - - - - -------------------------------------------------------------------------------------------------------------------------------
 PERL_DBD_TEST.PERL_DBD_T_COL_A_5530499751                     Y Y       COL_A
 PERL_DBD_TEST.tPERL_DBD_TEST                                            N.A.
 spt_ijdbc_mda.spt_ijdbc_mda_ind                                 Y       mdinfo,mdaver_end,srvver_end
 spt_jtext.spt_jtext_mdinfo_5440019382                           Y       mdinfo
 spt_jtext.tspt_jtext                                                    N.A.
 spt_mda.spt_mda_ind                                             Y       mdinfo,mdaver_end,srvver_end

sp__monusedtables

show io on used tables

AUTHOR

Mich Talebzadeh

DESCRIPTION

Shows details on table usage

USAGE

sp__monusedtables @owner, @objname

SAMPLE OUTPUT

1> sp__monusedtables

Displaying table statistics

 TableName                      Rows        Inserted    Updated     Deleted     LogicalReads LockRequests
 ------------------------------ ----------- ----------- ----------- ----------- ------------ ------------
 monIOQueue                               0           0           0           0            0            0
 monDeadLock                              0           0           0           0            0            0
 monErrorLog                              0           0           0           0            0            0
 monCachedObject                          0           0           0           0            0            0
 monProcessNetIO                          0           0           0           0            0            0
 monProcedureCache                        0           0           0           0            0            0
 monProcessActivity                       0           0           0           0            0            0
 monSysWorkerThread                       0           0           0           0            0            0
 monProcessWorkerThread                   0           0           0           0            0            0
 monSysPlanText                           0           0           0           0            0            0
 monCachedProcedures                      0           0           0           0            0            0
 monWaitClassInfo                         0           0           0           0            0            0
 monState                                 0           0           0           0            0            0
 monTables                                0           0           0           0            0            0
 monTableColumns                          0           0           0           0            0            0
 monTableParameters                       0           0           0           0            0            0
 monCachePool                             0           0           0           0            0            0
 monSysStatement                          0           0           0           0            0            0
 monSysWaits                              0           0           0           0            0            0
 monOpenDatabases                         0           0           0           0            0            0
 monProcessLookup                         0           0           0           0            0            0
 monProcessProcedures                     0           0           0           0            0            0
 monSysSQLText                            0           0           0           0            0            0
 monProcessSQLText                        0           0           0           0            0            0
 monLocks                                 0           0           0           0            0            0
 monProcessWaits                          0           0           0           0            0            0
 monEngine                                0           0           0           0            0            0
 monNetworkIO                             0           0           0           0            0            0
 monDeviceIO                              0           0           0           0            0            0
 monDataCache                             0           0           0           0            0            0
 monProcessStatement                      0           0           0           0            0            0
 monProcessObject                         0           0           0           0            0            0
 monWaitEventInfo                         0           0           0           0            0            0
 monProcess                               0           0           0           0            0            0
 syblicenseslog                        1128          11           0           0           20           23
 monOpenObjectActivity                    0           0           0           0            0            0
 spt_monitor                              1           0           0           0            7            0
 spt_jdbc_conversion                     20           0           0           0            7            0
 spt_limit_types                          4           0           0           0           12            0
 spt_jdbc_table_types                     3           0           0           0           11            0
 spt_ijdbc_conversion                    20           0           0           0           12            0
 jdbc_function_escapes                   89           0           0           0           12            0
 spt_ijdbc_table_types                    3           0           0           0           12            0
 ijdbc_function_escapes                  88           0           0           0           12            0
 CWMM_Remote_Access                       1           0           0           0           14            2
 spt_ijdbc_mda                          175           0           0           0           11            0
 spt_mda                                183           0           0           0         2016         1910
 PERL_DBD_TEST                            0           0           0           0           12            0
 spt_jtext                                1           0           0           0           11            0
 spt_values                            1745           0           0           0       150647       15054

sp__monwaits

show wait info

AUTHOR

Edward Barlow (SQL Technologies)

DESCRIPTION

Show server wait info

This procedure uses vmstat/iostat format - you can run it either once or in a loop (@num_iter times with @num_sec_delay seconds between runs).

USAGE

sp__monwaits @num_sec_delay, @num_iter, @dont_format

SAMPLE OUTPUT

 Event                                    WaitTime    Waits
 ---------------------------------------- ----------- -----------
 xact coord: pause during idle loop           4001410       66688
 wait for buffer read to complete              532248    85475068
 wait for buffer write to complete              32235    15923057
 wait for buffer validation to complete          3680      162024
 wait for mass to stop changing                 30267    10315571
 wait for mass to finish changing               10774   502746724
 wait to acquire latch                          55839     7807761
 waiting for disk write to complete            115108    40656343
 waiting for disk write to complete             60530    11697654
 waiting for disk write to complete             27566     9398819
 waiting for disk write to complete             85976    50951106
 checkpoint process idle loop                  951594       16619
 hk: pause for some time                      2713933      530621
 wait for flusher to queue full DFLPIECE       151633      150265
 wait for data from client                       1933        6000
 wait until an engine has been offlined       1000427       33342
 wait for someone else to finish reading       372601    55500541
 waiting for semaphore                         391902    13636318
 waiting for CTLIB event to complete            87265   112396697
 waiting while allocating new client sock      997833       97155
 waiting while no network read or write i    11936037  1037354467
 waiting on run queue after yield              295191    72098512
 waiting on run queue after sleep              746636 -1313156962
 replication agent sleeping in retry slee        3839          64
 replication agent sleeping during flush      1840806    12733523
 waiting for incoming network data          201256524   265587239
 waiting for network send to complete         1175318   747115161
 waiting until last chance threshold is c        1144           3
 waiting for date or time in waitfor comm       68630         210
 waiting for lock on PLC                         1370     1224561

This output is documentation for the SQL Technologies EXTENDED STORED PROCEDURES.

The Complete GEM Documentation	Slide Show: What Is GEM	Download GEM

An Introduction To GEM	How To Install	Getting Started Guide
Batch Jobs Overview	GEM Screenshots	Server Backup and Maintenance
Utilities Guide	The GEM Console	New System Stored Procedures
FAQ and Change Log	Common Perl Libraries	Pricing & Right To Use

sp__depends	Better version of sp_depends
sp__help	Better sp_help
sp__helpdb	Database Information
sp__helpdevice	Break down database devices into a nice report
sp__helpgroup	List groups in database by access level
sp__helpindex	Shows indexes by table
sp__helpsegment	Segment Information
sp__helprotect	Simple Protection Info for the database
sp__helptext	Show comments with line splits ok
sp__helpuser	Lists users in current database by group (includes aliases)
sp__lock	Lock information
sp__syntax	Works on any procedure to give you syntax
sp__who	sp_who that fits on a page

sp__block	Blocking processes.
sp__dbspace	Summary of current database space information.
sp__dumpdevice	Listing of Dump devices
sp__diskdevice	Listing of Disk devices
sp__helpdbdev	Show how Databases use Devices
sp__helplogin	Show logins and remote logins to server
sp__helpmirror	Shows mirror information, discover broken mirrors
sp__segment	Segment Information
sp__server	Server summary report (very useful)
sp__stat	Give basic server performance information (loops)
sp__vdevno	Who's who in the device world

sp__badindex	list badly formed indexes (allow nulls) or those needing statistics
sp__collist	list all columns in database
sp__find_missing_index	Finds keys that do not have associated index
sp__flowchart	Makes a flowchart of procedure nesting
sp__groupprotect	Permission info by group
sp__indexspace	Space used by indexes in database
sp__id	Gives information on who you are and which db you are in
sp__noindex	list of tables without indexes.
sp__helpcolumn	show columns for given table
sp__helpdefault	list defaults (part of objectlist)
sp__helpobject	list objects
sp__helpproc	list procs (part of objectlist)
sp__helprule	list rules (part of objectlist)
sp__helptable	list tables (part of objectlist)
sp__helptrigger	list triggers (part of objectlist)
sp__helpview	list views (part of objectlist)
sp__objprotect	Permission info by object
sp__read_write	list tables by # procs that read, # that write, # that do both
sp__trigger	Useful synopsis report of current database trigger schema
sp__whodo	sp__who - filtered for only active processes

sp__auditsecurity	Security Audit On Server
sp__auditdb	Audit Current Database For Potential Problems
sp__checkkey	Generate script for referential integrity problems (uses key info from sp_foreignkey)

sp__revalias	get alias generation script for current database
sp__revdb	get database generation script for server
sp__revdevice	get device generation script for server
sp__revgroup	get group generation script for current database
sp__revindex	get index generation script for current database
sp__revlogin	get login generation script for server
sp__revmirror	get mirror generation script for current database
sp__revsegment	get segment generation script for current database
sp__revtable	get table generation script for current database
sp__revuser	get user generation script for current database

sp__bcp	Create unix script to bcp in/out database
sp__date	Who can remember all the date styles?
sp__iostat	Loops n times showing active processes only
sp__grep	Search for patern
sp__isactive	Shows info about a single active process
sp__ls	Lists specific objects
sp__quickstats	Quick dump of server summary information
sp__whoactive	Show info about who is active

EXTENDED STORED PROCEDURES

Stored procedure library

Installation

Special thanks

Right to use, resale and copyright

Uninstall

Warranty

List of procedures

GENERAL HELP PROCEDURES

SYSTEM ADMINISTRATOR PROCEDURES

DBA PROCEDURES

AUDIT PROCEDURES

REVERSE ENGINEERING PROCEDURES

OTHER PROCEDURES

Auditing error codes

sp__auditdb

Audit current database

AUTHOR

DESCRIPTION

USAGE

SEE ALSO

ACCESS

SAMPLE OUTPUT

sp__auditsecurity

Audit system security

AUTHOR

DESCRIPTION

USAGE

SEE ALSO

ACCESS

SAMPLE OUTPUT

sp__badindex

List badly formed indexes or those needing statistics

AUTHOR

DESCRIPTION

USAGE

SAMPLE OUTPUT

sp__bcp

Produce object

AUTHOR

DESCRIPTION

USAGE

BUGS

SAMPLE OUTPUT

sp__block

Show blocked processes details

AUTHOR

DESCRIPTION

SEE ALSO

SAMPLE OUTPUT

sp__checkkey

Creates script you can use to check db referential integrity.

AUTHOR

DESCRIPTION

SAMPLE OUTPUT

sp__colconflict

Analyze conflicting columns definitions in current database

AUTHOR

DESCRIPTION

USAGE

SEE ALSO

SAMPLE OUTPUT

sp__collist

Analyze columns in current database

AUTHOR

DESCRIPTION

USAGE

SEE ALSO

SAMPLE OUTPUT

sp__colnull

columns with conflicting nullity

AUTHOR

DESCRIPTION

USAGE

SEE ALSO

SAMPLE OUTPUT

sp__configure

a better system configuration viewer

AUTHOR

DESCRIPTION