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.plignoring 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!
| 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 |
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__auditdbAudit current database |
Edward Barlow ( SQL Technologies, inc. )
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
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.
This procedure can be only runable by sa because it may reveal information that can help an intruder..
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__auditsecurityAudit system security |
Edward Barlow ( SQL Technologies, inc. )
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"....
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.
This procedure is only runable by sa because it reveals users with weak passwords.
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__badindexList badly formed indexes or those needing statistics |
Edward Barlow ( SQL Technologies, inc. )
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.
sp__badindex [ @tablename ]
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__bcpProduce object |
Simon Walker, The SQL Workshop LTD.
Creates bcp in / out shell script that can be used to extract info from the database.
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)
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.
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__blockShow blocked processes details |
4.9 version: Simon Walker, The SQL Workshop LTD. System 10 version: Ed Barlow
Monitor Blocked Processes
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__checkkeyCreates script you can use to check db referential integrity. |
Ed Barlow
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.
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__colconflictAnalyze conflicting columns definitions in current database |
Edward Barlow ( SQL Technologies, inc. )
Reports column with multiple definitions (for example, one. defined in table A as an int and in B as a smallint).
sp__colconflict [ @objectname ]
@objname specifies objects to select (query like "%@objname%")
sp__helpnull
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__collistAnalyze columns in current database |
Edward Barlow ( SQL Technologies, inc. )
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.
sp__collist [ @objectname ]
@objname specifies objects to select (query like "%@objname%")
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__colnullcolumns with conflicting nullity |
Edward Barlow ( SQL Technologies, inc. )
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).
sp__colnull [ @objectname ]
@objname specifies objects to select (query like "%@objname%")
sp__helpconflict
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__configurea better system configuration viewer |
Edward Barlow ( SQL Technologies, inc. )
This, unlike sp_configure, gives straight result set viewing, sorted into categories. It also shows any defaults if available.
Proc_name Order Parameter ------------------------------ ----- ------------------------------ sp__configure 1 @dont_format char(1) NOT NULL
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__dateShow date conversion formats for the server |
Simon Walker, The SQL Workshop LTD.
show date conversion formats for server. you could look it up but...
sp__date [ @datestring ]
@datestring is date string to convert - if not given uses getdate()
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__datediffGives real datediff between time and now |
Edward Barlow ( SQL Technologies, inc. )
@starttime datetime
@scale char(1)
@outp float output
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()))
1> declare @x float 2> exec sp__datediff "Jan 20 1999","h",@x
sp__dbspaceShow current db space |
Unknown
calculates out amounts reserved and used for current database
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__dbuseShow system information |
Philippe Wathelet (Flexible Consulting) philipew@hotmail.com )
5.2
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.
......:1>sp__dbuse <database name> ......:2>go
From any database including
OR
OR
If the current DB is master,
Q Vincent Yin (umyin@mctrf.mb.ca), Sep 1995
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:
For example,
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:
@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.
Edward Barlow ( SQL Technologies, inc. )
Basic information about disk devices
sp__diskdevice [@devname]
@devname: device name you are interested in - defaults to printing all dump devices
sp__helpdevice, sp__dumpdevice
Edward Barlow ( SQL Technologies, inc. )
Basic information about dump devices
sp__dumpdevice [@devname]
@devname: device name you are inetested in - defaults to printing all dump devices
sp__helpdevice, sp__diskdevice
Edward Barlow ( SQL Technologies, inc. )
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.
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.
Edward Barlow ( SQL Technologies, inc. )
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...
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).
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.
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).
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}'
Edward Barlow ( SQL Technologies, inc. )
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.
sp__helprotect, sp__objprotect
Edward Barlow ( SQL Technologies, inc. )
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....
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)
sp__help calls the procedures sp__helpcolumn and sp__helpindex when an object is passed as a parameter.
Edward Barlow ( SQL Technologies, inc. )
List columns for given table
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.
1> sp__helpcolumn server
1> exec sp__helpcolumn
Edward Barlow ( SQL Technologies, inc. )
Show standard database information in a concise format
The following procedures are especially useful while creating new databases and attempting to perform optimal allocation of space: sp__helpdb, sp__helpdbdev, sp__helpdevice
sp_helpdb shows information about all databases
sp__helpdb [ @dbname ] prints specific information about given database.
Edward Barlow ( SQL Technologies, inc. )
Show device to database breakdown. Which devices are used by database.
The following procedures are especially useful while creating new databases and attempting to perform optimal allocation of space: sp__helpdb, sp__helpdevice, sp__helpdbdev.
sp__helpdbdev [ @dbname ]
if @dbname parameter is passed, only show information for given database
Edward Barlow ( SQL Technologies, inc. )
list defaults information in current database
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.
sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview
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.
Edward Barlow ( SQL Technologies, inc. )
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.
The following procedures are especially useful while creating new databases and attempting to perform optimal allocation of space: sp__helpdevice, sp__helpdbdev, sp__helpdb.
sp__helpdevice summary information on all devices sp__helpdevice @devicename details for particular device
Edward Barlow ( SQL Technologies, inc. )
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.
sp__helpgroup [ @groupname ]
if @groupname provided, it will only provide information about that specific group.
Edward Barlow ( SQL Technologies, inc. )
show index information in current database
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).
Edward Barlow ( SQL Technologies, inc. )
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.
sp__helplogin [ @loginname ]
Edward Barlow ( SQL Technologies, inc. )
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.
Edward Barlow ( SQL Technologies, inc. )
list tables, views, procedures, rules, defaults, and triggers in current database
sp__helpobject [ @objectname ]
if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.
sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview
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.
Edward Barlow ( SQL Technologies, inc. )
list procedure information in current database
sp__helpproc [ @objectname ]
if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.
sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview
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.
Edward Barlow ( SQL Technologies, inc. )
list protection information for current database
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.
sp__groupprotect, sp__objprotect
helprotect should really be spelled helpprotect, but im sticking with the sybase naming convention.
Edward Barlow ( SQL Technologies, inc. )
list rules information in current database
sp__helprule [ @objectname ]
if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.
sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview
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.
shows segments information by database. Also shows sizes of segments.Used in conjunction with sp__segment procedure to decipher segment heirarchy.
sp__helpsegment [ @segname ]
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
Edward Barlow ( SQL Technologies, inc. )
list table information in current database
sp__helptable [ @objectname ]
if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.
sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview
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.
Andrew Zanevsky, AZ Databases, Inc.
list text of code. Uses print statement for technical reasons.
Edward Barlow ( SQL Technologies, inc. )
list trigger information in current database
sp__helptrigger [ @objectname ]
if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.
sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview. Another way to look at triggers is sp__trigger.
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.
Edward Barlow ( SQL Technologies, inc. )
There is no easy way to look at user defined types using the current system procs, so here one is.
Edward Barlow ( SQL Technologies, inc. )
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.
Edward Barlow ( SQL Technologies, inc. )
list views information in current database
sp__helpview [ @objectname ]
if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.
sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview
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.
Edward Barlow
DESCRIPTION:
Tells you who you are and in which database you are in
Edward Barlow ( SQL Technologies, inc. )
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.
sp__indexspace [ @table ]
Edward Barlow ( SQL Technologies, inc. )
sp__whoactive, sp__isactive, sp__iostat
Unix iostat command equivalent. Does a loop on existing processes and prints only rows in sysprocesses that are doing anything (io or cpu > 0).
Does not monitor logins started after process starts.
sp__iostat { [ @count ] [, @delay ]
1> exec sp__iostat
Androw Zanevsky, AZ Databases, Inc. 71232.3446@compuserve.com
sp__whoactive, sp__isactive, sp__iostat
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.
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
> sp__isactive 1
Simon Walker, The SQL Workshop LTD., Edward Barlow ( SQL Technologies, inc. )
pretty print locks
Edward Barlow ( SQL Technologies, inc. )
pretty print table locks
Only shows correct object name for objects in current database. If the
Edward Barlow ( SQL Technologies, inc. )
Unix ls equivalent. lists objects in db
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.
Simon Walker, The SQL Workshop LTD.
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.
Edward Barlow ( SQL Technologies, inc. )
Gives number of select / update /delete /insert / revoke / and execute grants for each object. Can either be by user (parameter) or total.
sp__groupprotect, sp__helprotect
Edward Barlow ( SQL Technologies, inc. )
Returns Extended Stored Procedure Library version number
Edward Barlow ( SQL Technologies, inc. )
sp__dbspace has had locking problems in busy environments when run programaticly. Use this procedure if you are running space monitoring in a loop.
Edward Barlow ( SQL Technologies, inc. )
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.
sp__quickstats { @starttime }
if @starttime is not passed, will not print out a date field at the end.
Edward Barlow ( SQL Technologies, inc. )
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.
Edward Barlow ( SQL Technologies, inc. )
reverse engineers aliases
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser
WARNING: Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
Edward Barlow ( SQL Technologies, inc. )
Reverse engineer bindings of user defined types to columns
Edward Barlow ( SQL Technologies, inc. )
reverse engineers layout of the databases on a server. Purposly excludes device "master", "model", and "tempdb".
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser
WARNING: Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
Edward Barlow ( SQL Technologies, inc. )
reverse engineers layout of the devices on a server. Purposly excludes device "master", "diskdump", "tapedump1", and "tapedump2".
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser
WARNING: Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
Edward Barlow ( SQL Technologies, inc. )
reverse engineers layout of the groups in your current database
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser
WARNING: Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
Edward Barlow ( SQL Technologies, inc. )
reverse engineers indexes in your current database
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser WARNING:
Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
Created By : "Chris Vilsack"
reverse engineers keys in your current database.
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser, sp__revtable
WARNING: run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
Edward Barlow ( SQL Technologies, inc. )
reverse engineers logins in your current server. Can only be run by sa.
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser WARNING:
Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
Edward Barlow ( SQL Technologies, inc. )
reverse engineers mirror information
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser WARNING:
Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
Edward Barlow ( SQL Technologies, inc. )
Created By : "Chris Vilsack"
reverse engineers rules in your current database.
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser, sp__revtable
WARNING: run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
Edward Barlow ( SQL Technologies, inc. )
reverse engineers segment layout in your current database
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser, sp__revsegment WARNING:
Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
Edward Barlow ( SQL Technologies, inc. ). Modified by Victor Go and Viorel Vlad to add the constraint stuff.
reverse engineers table layout in your current database. This version handles default, check, and primary key constraints.
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser, sp__revtable
WARNING: run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
reverse engineers types in your current database
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revtype
Edward Barlow ( SQL Technologies, inc. )
reverse engineers user layout in your current database
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser
Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
Edward Barlow ( SQL Technologies, inc. )
show segmentation
sp__segment [ @segment ]
if @segment is passed, will only print info for the given segment
Edward Barlow ( SQL Technologies, inc. )
server summary report
The following procedures are called by sp__server: sp__helpdb, sp__helpdbdev, sp__devspace, sp__helpdevice, sp__helpmirror, sp__vdevno, sp__helpsegment, sp__helplogin.
Edward Barlow ( SQL Technologies, inc. )
(11 rows affected) (return status = 0)
Edward Barlow ( SQL Technologies, inc. )
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).
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.
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.
Edward Barlow ( SQL Technologies, inc. )
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.
sp__syntax [@procname]
you probably should pass in a parameter, otherwise you will get gobs of output.
Simon Walker, The SQL Workshop LTD.
show all tables/triggers in current database
sp__trigger [@tablename]
Edward Barlow ( SQL Technologies, inc. )
Simon Walker, The SQL Workshop LTD.
Shows Used Device Numbers
Simon Walker, The SQL Workshop LTD. Ed Barlow
pretty version of sp_who
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.
1> exec sp__who MyDb
spid logi hostinfo proc dbname status cmd bk
Androw Zanevsky, AZ Databases, Inc. 71232.3446@compuserve.com
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.
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
Total: 5 process(es). (* - active, @ - this process.)
Simon Walker & Ed Barlow (Ed rewrote Simons command)
sp__who that drops awaiting commands and the sa stuff
sp__whoactive, sp__isactive, sp__iostat, sp__who
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.
Philippe Wathelet July 1998
This is a substitute for Sybase's standard sp_who stored procedure
The output is clearer without line wrapping. You can help it with:
allowing a width of up to 150 characters per line for example
sp__whoe [ @login ]
if @login is passed, it will only print information for that login.
Edward Barlow (SQL Technologies)
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.
Edward Barlow (SQL Technologies)
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).
sp__moncache @num_sec_delay, @num_iter, @dont_format
1> sp__moncache
1> sp__moncache 3,3
Edward Barlow (SQL Technologies)
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
exec sp__mondump
extensive output... run it and see
Edward Barlow (SQL Technologies)
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).
sp__monengine @num_sec_delay, @num_iter, @dont_format
1> sp__monengine 3,3
Edward Barlow (SQL Technologies)
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).
sp__monio @num_sec_delay, @num_iter, @dont_format
1> sp__monio
1> sp__monio 3,3
Edward Barlow (SQL Technologies)
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).
sp__monlock @num_sec_delay, @num_iter, @dont_format
1> sp__monlock
1> sp__monlock 3,3
Edward Barlow (SQL Technologies)
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).
sp__monlocksql @dont_format
Edward Barlow (SQL Technologies)
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).
sp__monnet @num_sec_delay, @num_iter, @dont_format
1> sp__monnet
1> sp__monnet 3,3
Edward Barlow (SQL Technologies)
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).
sp__monobj @num_sec_delay, @num_iter, @dont_format, @object_name
1> sp__monobj
1> sp__monobj 3,3
Edward Barlow (SQL Technologies)
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).
sp__monopenobj @num_sec_delay, @num_iter, @dont_format, @object_name
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
Edward Barlow (SQL Technologies)
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).
sp__monpwaits @SPID, @num_sec_delay, @num_iter, @dont_format
Event WaitTime Waits
1> sp__monpwaits 3,3
Mich Talebzadeh
Show proceecures that are running for more than 100 ms
Edward Barlow (SQL Technologies)
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
exec sp__monserver
Edward Barlow (SQL Technologies)
show everything about a running spid . Can be used to investigate locks and blocks and slow running procs etc...
sp__monspid 765
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
Edward Barlow (SQL Technologies)
Shows currently executeing sql statements if no spid is passed and shows all sql history if spid is passed
sp__monsql @spid, @dont_format
Mich Talebzadeh
Shows comprehensive information about how tables are used in the db
Tables accessed with Table scans ONLY, no index usage
Displaying dormant tables with no DML activity, table scan or index usage
Indexes never selected or used by the optimizer
Displaying tables with DML activity
Tables accessed with Table scans and index usage as well
Index usage analysis
Mich Talebzadeh
Top processes in use ordered by cputime
sp__montopn @spid
1> sp__montopn
Process Login Application Command DBName EngineNumber MemUsageKB CpuTime WaitTime ObjectType ObjectName
Edward Barlow (SQL Technologies)
sp__monunusedindex @dont_format, @no_print
Mich Talebzadeh
Shows details on table usage
sp__monusedtables @owner, @objname
Displaying table statistics
Edward Barlow (SQL Technologies)
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).
sp__monwaits @num_sec_delay, @num_iter, @dont_format
......:1>sp__dbuse
......:2>go
master:1>sp__dbuse
master:2>go
SEE ALSO
SAMPLE OUTPUT
----------------------------------------------
You are 'SYS_INSTALL' as 'dbo' under SPID 10
on with role(s): sa, sso, oper
-DATABASE: Monday Oct 11 1999 11:18AM
========
Database: xrm_db dbid:5
DB owner: xrm_mgr suid:6
Creation: Apr 1 1999 9:42AM MB: 52
-PLACEMENT:
=========
id Database id Database
-- ------------------- -- -------------------
4 sybsystemprocs
5 xrm_db <<<<<<<<<<<<
6 xrm_sod01_db
7 xrm_security_db
8 sim_db
-OPTIONS:
=======
- Select into/bulkcopy/pllsort
- Trunc log on chkpt
- Abort tran on log full
-IN USE BY:
=========
All DBs # # # # # # # # # # # # # # # # # #
This DB xrm_db is NOT in use
-ROLES:
=====
sa sso oper replication
---------- ---------- ---------- ------------
sa
SYS_OPER
SYS_INSTAL
SCRIPT_SVR
-SERVER:
======
Running ####################################
Idle ##################################
SQL #############
I/O ####################################
Received ####################################
Sent ###############################
Errors: 2
Read #######
Write ####################################
Errors: 0
-ENGINES:
=======
Nr Status # Pr Online since
0 online 0 Oct 6 1999 11:48AM
-DATA:
====
Size MB Used MB Full % Free MB
40 30.8 77.1 9.2
-LOG:
===
Size MB Used MB Full % Free MB
12 0.1 0.8 11.9
-ALLOCATED:
=========
Device Usage Size MB
datadev1 - Data - 40
logdev1 - Log - 10
logdev1 - Log - 2
-AVAILABLE: (max used Virtual Device Nr = 3 )
=========
Device VDN Total MB Free MB
datadev1 2 28
logdev1 3 13
master 0 1
sysprocsdev 1 0
+ ------ + -----
387 42
----------------------------------------------
see also 'sp__dbuse usage'
sp__depends
It's a superset of sp_depends.
AUTHOR
DESCRIPTION
If @format=null, output is in tabular format similar to sp_depends.
If @format='drop', output is in isql format.
exec sp__depends 'my_rule', 'drop'
Msg 3716, Level 16, State 1:
The rule 'my_rule' cannot be dropped because it is
bound to one or more column.
BUGS
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
DESCRIPTION
USAGE
SEE ALSO
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
DESCRIPTION
USAGE
SEE ALSO
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
DESCRIPTION
USAGE
SAMPLE OUTPUT
1> exec sp__find_missing_index
No Indexes Found in Current Database
sp__flowchart
List execution flow of procedures
AUTHOR
DESCRIPTION
USAGE
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
USAGE
SAMPLE OUTPUT
sp__groupprotect
Synopsis of protection stuff.
AUTHOR
DESCRIPTION
SEE ALSO
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
DESCRIPTION
USAGE
SEE ALSO
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
DESCRIPTION
USAGE
SEE ALSO
SAMPLE OUTPUT
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
DESCRIPTION
SEE ALSO
USAGE
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
DESCRIPTION
SEE ALSO
USAGE
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
DESCRIPTION
USAGE
SEE ALSO
BUGS
SAMPLE OUTPUT
1> sp__helpdefault
Default Name Times Used Definition
-------------------- ---------- -------------------------------------
sp__helpdevice
Break down database devices into a nice report
AUTHOR
DESCRIPTION
SEE ALSO
USAGE
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
DESCRIPTION
USAGE
SEE ALSO
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
DESCRIPTION
USAGE
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
DESCRIPTION
USAGE
SEE ALSO
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
DESCRIPTION
USAGE
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
DESCRIPTION
USAGE
SEE ALSO
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
DESCRIPTION
USAGE
SEE ALSO
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
DESCRIPTION
USAGE
SEE ALSO
BUGS
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
DESCRIPTION
USAGE
SEE ALSO
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
USAGE
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"
sp__helptable
list table information in current databases
AUTHOR
DESCRIPTION
USAGE
SEE ALSO
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
DESCRIPTION
USAGE
sp__helptrigger
list trigger information in current databases
AUTHOR
DESCRIPTION
USAGE
SEE ALSO
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
DESCRIPTION
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
DESCRIPTION
SEE ALSO
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
DESCRIPTION
USAGE
SEE ALSO
BUGS
SAMPLE OUTPUT
1> sp__helpview
View Name Cr Date Tables Used
-------------------- ---------- --------------------------------------
sp__id
Tells you who you are and in which database
AUTHOR
USAGE
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
DESCRIPTION
USAGE
SEE ALSO
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
SEE ALSO
DESCRIPTION
BUGS
USAGE
@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
-------- ---------
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
SEE ALSO
DESCRIPTION
USAGE
SAMPLE OUTPUT
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
DESCRIPTION
USAGE
SEE ALSO
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
DESCRIPTION
USAGE
SEE ALSO
SAMPLE OUTPUT
1> exec sp__lockt
spid dbname objname type
---- ---------------- ---------------- ----------------
1 master spt_values Sh_intent
BUGS
sp__ls
Equivalent of the unix ls command
AUTHOR
DESCRIPTION
USAGE
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
DESCRIPTION
USAGE
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
DESCRIPTION
SEE ALSO
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
DESCRIPTION
SAMPLE OUTPUT
1> exec sp__proclib_version
-----
3.80
sp__qspace
a faster version of sp__dbspace
AUTHOR
DESCRIPTION
SEE ALSO
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
DESCRIPTION
USAGE
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
DESCRIPTION
USAGE
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
DESCRIPTION
USAGE
SEE ALSO
SAMPLE OUTPUT
1> sp__revalias
Text
-----------------------------------------------------------------------
exec sp_addalias 'probe','dbo'
exec sp_addalias 'ebarlow','dbo'
sp__revbindings
reverse engineer bindings in db
AUTHOR
DESCRIPTION
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
DESCRIPTION
USAGE
SEE ALSO
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
DESCRIPTION
USAGE
SEE ALSO
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
DESCRIPTION
USAGE
SEE ALSO
SAMPLE OUTPUT
1> sp__revgroup
-------------------------------------------------
exec sp_addgroup 'developer'
exec sp_addgroup 'user'
sp__revindex
Reverse engineer indexes in current database
AUTHOR
DESCRIPTION
USAGE
SEE ALSO
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
DESCRIPTION
USAGE
SEE ALSO
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
DESCRIPTION
USAGE
SEE ALSO
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
DESCRIPTION
USAGE
SEE ALSO
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
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
DESCRIPTION
USAGE
SEE ALSO
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
DESCRIPTION
USAGE
SEE ALSO
SAMPLE OUTPUT
1> sp__revsegment
-------------------------------------------
exec sp_addsegment 'yyy','datadevice'
sp__revtable
Reverse engineer tables of current server / database
AUTHOR
DESCRIPTION
USAGE
SEE ALSO
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
USAGE
SEE ALSO
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
DESCRIPTION
USAGE
SEE ALSO
WARNING
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
DESCRIPTION
USAGE
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
DESCRIPTION
USAGE
SEE ALSO
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
DESCRIPTION
SEE ALSO
USAGE
Proc_name Order Parameter
------------------------------ ----- ------------------------------
sp__size 1 @objname varchar(40) NOT NULL
(return status = 0)SAMPLE OUTPUT
Proc_name Size Avail_size Lines Avail_lines
------------------------ ----- ----------- ----------- -----------
mon_authorize_non_sa 6 KB 122 2 253
mon_rpc_attach 3 KB 125 1 254
mon_rpc_connect 2 KB 126 1 254
sp_configure 180KB -52 73 182
sp_dboption 116KB 12 49 206
sp_dbupgrade 73 KB 55 19 236
sp_getmessage 33 KB 95 14 241
sp_loaddbupgrade 6 KB 122 2 253
sp_procxmode 28 KB 100 12 243
sp_prtsybsysmsgs 4 KB 124 1 254
sp_validlang 6 KB 122 2 253
sp__stat
Sybase iostat/vmstat equivalent for server
AUTHOR
DESCRIPTION
USAGE
NOTES
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
DESCRIPTION
USAGE
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
DESCRIPTION
USAGE
SEE ALSO
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
DESCRIPTION
1> sp__uptime
name size_data size_log owner
---------- ---------- -------- ----------
statsdb 7.000000 2.000000 sa
sp__vdevno
Show used device numbers
AUTHOR
DESCRIPTION
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
DESCRIPTION
USAGE
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
---- --------- -------- ---- ---------- -------- ---------------- --
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
DESCRIPTION
USAGE
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
sp__whodo
Check who is doing something on server
AUTHOR
DESCRIPTION
SEE ALSO
USAGE
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
DESCRIPTION
isql -U<user> -S<server> -w150
USAGE
SAMPLE OUTPUT
1> exec sp__whoe
sp__monbackup
show last backup information
AUTHOR
DESCRIPTION
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
DESCRIPTION
USAGE
- @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
CacheName PhysicalReads PhysicalWrites LogicalReads HitRatio
------------------------------ ------------- -------------- ------------ -------------
default data cache 6412018 2486 982314677 99.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
DESCRIPTION
USAGE
SAMPLE OUTPUT
sp__monengine
show engine usage from MDA tables
AUTHOR
DESCRIPTION
USAGE
- @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
EngineNumber AffinitiedToCPU Status Connections
------------ --------------- -------------------- -----------
0 0 online 30
1 1 online 29
2 2 online 31
3 3 online 28
4 4 online 28
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
DESCRIPTION
USAGE
- @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
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
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
DESCRIPTION
USAGE
- @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
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
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
DESCRIPTION
USAGE
- @dont_format : char(1): no output formating if its not null
SAMPLE OUTPUT
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
DESCRIPTION
USAGE
- @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
PacketsSent PacketsReceived BytesSent BytesReceived
----------- --------------- ----------- -------------
36173115 2753319 1124048925 661268239
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
DESCRIPTION
USAGE
- @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
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
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
DESCRIPTION
USAGE
- @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
sp__monpwaits
show wait information
AUTHOR
DESCRIPTION
USAGE
- @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
---------------------------------------- ----------- -----------
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
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
DESCRIPTION
USAGE
SAMPLE OUTPUT
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
DESCRIPTION
USAGE
SAMPLE OUTPUT
sp__monspid
show everything about running spid
AUTHOR
DESCRIPTION
USAGE
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
------------------------------ ----------------------------------------
sp__monsql
show running sql on the server
AUTHOR
DESCRIPTION
USAGE
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
DESCRIPTION
USAGE
SAMPLE OUTPUT
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
TableName Rows Size/KB LogicalReads LockRequests
------------------------------ ------------- ------------- ------------ ------------
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
TableName Rows Size/KB Inserted Updated Deleted LockRequests SUM DML ACTIVITY/ROWS
------------------------------ ------------- ------------- ----------- ----------- ----------- ------------ ---------------------------
dbo.syblicenseslog 1128 32 11 0 0 23 0.0097517
TableName Rows Size/KB LogicalReads PagesRead Table Scans Index Usage IndexUsage/TableScan
------------------------------ ------------- ------------- ------------ ----------- ----------- ----------- --------------------
dbo.spt_values 1745 94 150647 870 496 6191 12.48
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
DESCRIPTION
USAGE
SAMPLE OUTPUT
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
DESCRIPTION
USAGE
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
DESCRIPTION
USAGE
SAMPLE OUTPUT
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
DESCRIPTION
USAGE
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.
copyright © 1994-2008 By
SQL Technologies