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